How to find missing id in the table

I have column looks like below

SID101
SID102
SID103
SID105
SID107

In the above criteria i need to find missed SID numbers. SID104 and SID 106 are missed while ordering.

How can i find the mi开发者_C百科ssed id numbers.Could any one help me finding it.

Thanks in advance.

If your table contains gaps with length more than 1 item, you can use this query:

declare @t table(s varchar(20))

insert @t values ('SID101'),('SID102'),('SID103'),('SID105'),('SID108');

with cte as
(
    select substring(t.s, 4, len(t.s)) [i]
    from @t t
)
select 'SID' + cast(m.number as varchar(20))
from master..spt_values m
left join cte c on c.i = m.number
where [Type] = 'P' 
    and m.number >= (select min(i) from cte) 
    and m.number <= (select max(i) from cte)
    and c.i is null

Output:

-----------------------
SID104
SID106
SID107

Something like this should work:

DECLARE @i INT;
SET @i = 100;

CREATE TABLE #idsToCheck (checkId varchar(100));

WHILE (@i < 200)
    BEGIN
        INSERT INTO #idsToCheck VALUES ('SID' + CONVERT(varchar(100), @i));
        SET @i = @i + 1;
    END

SELECT * FROM #idsToCheck itc
    LEFT OUTER JOIN MainTable mt ON itc.checkId = mt.realId
    WHERE mt.realId = NULL

DROP TABLE #idsToCheck

... where MainTable is your table containing the SID101, SID102, etc. column values, and MainTable.realId is the column containing those IDs. Modify the @i initial value and number in the while loop condition based on which SIDs you want to check from/to.

It's difficult. With

SELECT COUNT(*),MAX(CAST(REPLACE(y.name,'SID','') AS INT)) AS col_max FROM
sys.objects x INNER JOIN sys.columns y ON x.object_id=y.object_id
WHERE x.name='<TABLE_NAME>'

you should know, how much columns are missing (i.e. COUNT(*) is 5 and col_max is 107)

When you have a table, which contains only one column with all possible IDs from 1 to max (i.e. 100,101,102,103,104,...,132) then you could do

SELECT * FROM (
    SELECT CAST(REPLACE(y.name,'SID','') AS INT) AS col_id FROM
    sys.objects x INNER JOIN sys.columns y ON x.object_id=y.object_id
    WHERE x.name='<TABLE_NAME>'
) a
RIGHT JOIN <TABLE_IDS> b ON a.col_id=b.id
WHERE a.col_id IS NULL AND b.id<=(
    SELECT MAX(CAST(REPLACE(y.name,'SID','') AS INT)) AS col_max FROM
    sys.objects x INNER JOIN sys.columns y ON x.object_id=y.object_id
    WHERE x.name='<TABLE_NAME>'
)

EDIT: sorry, I've seen just now, that these values aren't column names, but values. My solution will find missing column names

 Declare @St int declare @end int

     set @st = CAST( (select RIGHT( max(data),4) from orderno)as int) 
     set @end = CAST( (select RIGHT( min(data),4) from orderno)as int)   

     create table #temp(data int)

    while(@St <= @end )
    begin
 insert into #temp values(@St)
 set @St = @St +1
   end

  select * from orderno 
  select * from #temp
 select data from #temp where data not in (select cast(RIGHT(data,4))

declare @t table(s varchar(20))

insert @t values ('SID101'),('SID102'),('SID103'),('SID105'),('SID107');

with cte as
(
    select substring(t.s, 4, len(t.s)) [i]
    from @t t
)

select 'SID' + cast(t1.i + 1 as varchar(20))
from cte t1
join cte t2 on t2.i > t1.i
    and not exists(
        select 1
        from cte c3
        where c3.i > t1.i and c3.i < t2.i
    )
where t2.i <> t1.i + 1

Output:

-----------------------
SID104
SID106