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

## 2006-06-30

Subscribe to:
Post Comments (Atom)

## 1 comment:

Cool recursive version. I might change my standard way to use that method :)

Post a Comment