2006-08-07

Split a String using recursive SQL

While sitting under my peartree, having a smoke, looking at the lawn and deciding that I had to mow it. Thinking I just had to catch up on my feed and watch the news before I started up the lawnmower, it suddenly occured to me that rather than using a numberstable (CTE) to split a string you could use a recursive SQL. Why I came to think of it? All the time I read blogs about Lisp, functional languages and how everything is so much cooler if you just start to think recursively ... :-)
Here is the SQL server code, just cut and paste into a querywindow in Managment Studio and hit F5


DECLARE @Delimitedtext varchar(max);
DECLARE @Delimiter char(1);
SET @Delimitedtext = 'aaa,bbbb,cccc,dddd,';
SET @Delimiter = ',';

WITH Strings(s, r)
AS
(
SELECT
SUBSTRING(@Delimitedtext,1, CHARINDEX(@Delimiter, @Delimitedtext)-1) s,
SUBSTRING(@Delimitedtext,CHARINDEX(@Delimiter, @Delimitedtext)+1, len(@Delimitedtext)) r
UNION ALL
SELECT
SUBSTRING(r,1, CHARINDEX(@Delimiter, r)-1) s,
SUBSTRING(r,CHARINDEX(@Delimiter, r)+1, len(r)) r
FROM Strings
WHERE
CHARINDEX(@Delimiter, r) > 0
)
SELECT s FROM Strings


The query will yield this result:

s
-------------------
aaa
bbbb
cccc
dddd

(4 row(s) affected)


Have fun Querying (SQL is not defined as a programming language, so you really can't have fun programming SQL ;-))

1 comment:

Anonymous said...

NSU - 4efer, 5210 - rulez
[url=http://bk-magazin.com][/url]