Binaryworld - Tricks using a numbers table ... [ SQL Server -> T-SQL ]

Solving most problems using a relational technique is more elegant and better in terms of performance. This script shows few examples that uses a Numbers table to solve some common problems. Keep a table with Numbers & it will prove very handy.

Click here to copy the following block

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