Garry Garry - 6 months ago 17
SQL Question

SQL query for finding first missing sequence string (prefix+no)

T-SQL query for finding first missing sequence string (prefix+no)

Sequence can have a prefix + a continuing no.

ex sequence will be

ID
-------
AUTO_500
AUTO_501
AUTO_502
AUTO_504
AUTO_505
AUTO_506
AUTO_507
AUTO_508


So above the missing sequence is AUTO_503 or if there is no missing sequence then it must return next sequence.

Also starting no is to specified ex. 500 in this case and prefix can be null i.e. no prefix only numbers as sequence.

Answer

You could LEFT JOIN the id numbers on shifted(+1) values to find gaps in sequential order:

SELECT 
    MIN(a.offsetnum) AS first_missing_num
FROM 
(
    SELECT 500 AS offsetnum
    UNION
    SELECT CAST(REPLACE(id, 'AUTO_', '') AS INT) + 1
    FROM tbl
) a
LEFT JOIN
    (SELECT CAST(REPLACE(id, 'AUTO_', '') AS INT) AS idnum FROM tbl) b ON a.offsetnum = b.idnum
WHERE 
    a.offsetnum >= 500 AND b.idnum IS NULL

SQLFiddle Demo