CREATE TABLE Numbers ( Number smallint )
DECLARE @Number smallint
SELECT @Number = 0
WHILE @Number <= 1000
BEGIN
    INSERT Numbers VALUES ( @Number )
    SELECT @Number = @Number + 1
END
go

-- Examples:
-- To strip values out from a CSV string that contains only numbers
DECLARE @str varchar(255)
SELECT @Str = ',1,2,3,4,5,6,7,8,9,'
SELECT Number AS Value
FROM Numbers
WHERE CHARINDEX(  ',' + CONVERT(varchar, Number ) + ',' , @Str ) > 0

-- To generate dates between two ranges
DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate = '1999-10-24 00:00', @EndDate = '1999-12-01 00:00'
SELECT DATEADD( dd, Number , @StartDate ) AS IndDate
FROM Numbers
WHERE DATEADD( dd, Number , @StartDate ) <= @EndDate

-- In SQL70
SELECT @StartDate + Number AS IndDate
FROM Numbers
WHERE @StartDate + Number <= @EndDate

This page was last updated on May 01, 2006 04:28 PM.