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 !!!
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