SQL Numbers table using a Common table expression (CTE)

With SQL server 2005 Microsoft finally implemented recursive SQLs as described in the ISO-ANSI SQL3 standard (Implemented in DB2, Sybase and partly Oracle a "loooong" time ago).
This new and exciting feature dubbed CTE (Common table expression) is supposed to be particular useful for traversing trees and hierarchies using SQL.
Reading Joe Celcos SQL Programming style (Highly recommended) I came acorss a concept called "numbers table".
This is a table with a sequence of integer numbers. Such a table is quite useful for parsing strings and generating testdata.
Thinking of uses for CTE's I figured they could be used to create an ad-hoc numbers table. Such a construct will also serve as an easy to understand example of a recursive query.

WITH Numbers(n)
AS
(
SELECT 1 AS n
UNION ALL
SELECT (n + 1) AS n
FROM Numbers
WHERE
n < 1000
)
SELECT n from Numbers
OPTION(MAXRECURSION 1000) -- defaults 100

What happens in a recursive query is that the first select statement initializes the query and returns the first row(s),
the second query then executes for every row added to the result (Selecting from itself)
Thus selecting n incremented by one and adding the row(s) to the result.

To parse a string you could do it like this:


DECLARE @Delimitedtext varchar(max);
DECLARE @Delimiter char(1);
SET @Delimitedtext = ',aaa,bbbb,cccc,';
SET @Delimiter = ',';
WITH Numbers(n)
AS
(
SELECT 1 AS n
UNION ALL
SELECT (n + 1) AS n
FROM Numbers
WHERE
n < 1000 )
SELECT
SUBSTRING(@DelimitedText, Numbers.n + 1, CHARINDEX(@Delimiter, @DelimitedText,Numbers.n + 1) - Numbers.n - 1)
FROM Numbers
WHERE
Numbers.n <= LEN(@DelimitedText) - 1 AND
SUBSTRING(@DelimitedText, Numbers.n, 1) = @Delimiter
AND
LEN(SUBSTRING(@DelimitedText, Numbers.n + 1,CHARINDEX(@Delimiter, @DelimitedText, Numbers.n + 1) - Numbers.n - 1)) > 0
OPTION (MAXRECURSION 1000)

More fun with number tables can be found here