Raj,

Since you are showing a comma-delimited list, this solution will first break that list apart, and then show the missing numbers in this list. It will run faster if you have your own Tally table - see the article mentioned below for how to build your own.

declare @list varchar(50),

@Delimiter char(1);

set @list = '1,2,4,5,7,8,11,12,13,15,17,19,20';

set @Delimiter = ',';

-- first, need to break down into separate items.

-- See Jeff Moden's article The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

-- at http://www.sqlservercentral.com/articles/T-SQL/62867/ for how a tally table can split strings apart.

WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),

Thousands (N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),

Millions (N) AS (select 1 FROM Thousands t1 CROSS JOIN Thousands t2),

Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),

ItemSplit (ItemOrder, Item) AS (

SELECT N,

RTRIM(LTRIM(SUBSTRING(@Delimiter + @list + @Delimiter,N+1,CHARINDEX(',',@Delimiter + @list + @Delimiter,N+1)-N-1)))

FROM Tally

WHERE N < LEN(@Delimiter + @list + @Delimiter)

AND SUBSTRING(@Delimiter + @list + @Delimiter,N,1) = ','

)

SELECT t.N

FROM Tally t

LEFT JOIN ItemSplit ON t.N = ItemSplit.Item

WHERE ItemSplit.Item IS NULL

AND t.N < (select MAX(convert(int,Item)) from ItemSplit)

ORDER BY t.N

Edit: corrected name misspelling