Raj.. (4/28/2010)
I have the data like 1,2,4,5,7,8,11,12,13,15,17,19,20. How to find the missing numbers in the series?Please help me
Thanks in advance..!!
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
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes