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 jumped 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.
You can remove the letters of the code and then compare it with 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.
Non-numeric character removal reference: T-SQL select query to remove non-numeric characters