SQL Server - Find Missing Numbers in sequence where numbers contain preceding zeros

Question

I have numbers in sequence stored in database which are stored as text and contain preceding zeros.

Numbers
-------
001
002
003
004
006
007
010
011
-------

The query should find following result

Missing
-------
005
008
009
-------

Thanks.

what is the range of numbers you have? How are numbers greater than 100 stored? — AB_87, Oct 13 '17 at 05:36
Greater than 100 numbers are stored as 100,101 etc and maximum numbers is 9999 — sourabhgk, Oct 13 '17 at 05:40

score 2 · Accepted Answer · answered Oct 13 '17 at 05:47

All you need is a sequence of number table ( there are so many implementations already in SO) and then use LEFT JOIN. See below query: seq is sequence of number form 1 to 9999 as int.

;with seq as 
(
select top 9999 row_number() over(order by t1.number) as N
from   master..spt_values t1 
       cross join master..spt_values t2

)

SELECT RIGHT('000'+CAST(s.n AS VARCHAR(3)),3) as MissingNumbers
from seq s 
left join yourtable t on s.n = cast(t.Number as int)
where t.number is null

SQL Server - Find Missing Numbers in sequence where numbers contain preceding zeros

1 Answers1