sql-server

Get all Numbers that are not existing from a Microsoft SQL-VarChar-Column


I have a table with a Column for ID-Numbers that are not increased in single steps.

So there are Numbers that are not used and this i need. The Column is a VarChar-Column.

For Example:

Used Numbers in the table = 2, 5, 7, 9, 10 etc.

So i need a Query that gives me = 1, 3, 4, 6, 8 etc.

Pseudo-Code something like:

select numbers from Table NOT IN (select numbers from table)!

I have tried with NOT IN and NOT EXISTS, but nothing works.

Can someone help me to achieve this?

EDIT: Range of Numbers is from 0 to 99999999 !!!


Solution

  • DECLARE @Table AS TABLE
    (
        Id VARCHAR(5)
    )
    
    INSERT INTO @Table
    VALUES
     ('1')
    ,('3')
    ,('5')
    ,('7')
    ,('10')
    
    DECLARE @Range AS TABLE
    (
        RangeId VARCHAR(10)
    )
    
    INSERT INTO @Range
    SELECT TOP (1000000) n = CONVERT(VARCHAR(10), ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
    FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
    OPTION (MAXDOP 1)
    
    select 
        MissingId = RangeId
    from 
        @Range AS R
        LEFT OUTER JOIN @Table AS T ON T.Id = R.RangeId
    WHERE
        CONVERT(INT,R.RangeId) <= (SELECT MAX(CONVERT(INT,Id)) FROM @Table)
        AND T.Id IS NULL
    order by MissingId