SQL to check skipped number

Asked

Viewed 632 times

0

I have a table with 12,560 records from 5 years ago, that is, it does not have such a good modeling and it has a field called 'code' that the user filled with something like CA-0001. The problem is that at some point in the history of this database someone skipped a number and I need to know what that number was. What query would I run to find out which line was skipped?

NOTE: I am using SQL Server 2014.

sql sql-server query

  • Use an sql of type select * from table t1 Where seq > 1 and not exists (select null from table t2 Where t2.seq = (t1.seq-1))

  • The problem is that the field is in varchar

  • 2

    Use functions like SUBSTRING to get the numeric part and CAST or CONVERT to convert the string to number , CONCAT to recompose the new string

  • Turns into an answer

  • @Márcioeric: Search by algorithms of gaps and Islands.

  • 1

    No boy, so I can score as solution. It was on record 133

  • The @Sorack did it.

Show 2 more comments

1 answer

0


You can remove the letters from the code and then compare it to the same table using NOT EXISTS:

SELECT codigo
  FROM tabela t
 WHERE NOT EXISTS(SELECT
                    FROM tabela t2
                   WHERE CAST(LEFT(SUBSTRING(t1.codigo, PATINDEX('%[0-9]%', t1.codigo), 8000), PATINDEX('%[^0-9]%', SUBSTRING(t1.codigo, PATINDEX('%[0-9]%', t1.codigo), 8000) + 'X') -1) AS INT) - 1
                       = CAST(LEFT(SUBSTRING(t2.codigo, PATINDEX('%[0-9]%', t2.codigo), 8000), PATINDEX('%[^0-9]%', SUBSTRING(t2.codigo, PATINDEX('%[0-9]%', t2.codigo), 8000) + 'X') -1) AS INT) - 1)

The above solution will show the record that has no predecessor.

Reference to the removal of non-numeric characters: T-SQL select query to remove non-numeric characters

Browser other questions tagged sql sql-server query

You are not signed in. Login or sign up in order to post.