One of the common questions that comes up in my CTE class/presentation asks can you use the CTE inside of a function or stored procedure. The answer is yes, but today I came across another question, how can I split a string (varchar) into rows. I did some searching but didn’t find quite what I was looking for. I did fine one CTE example, but I didn’t like the way it worked, so I decided to create my own Split function, using a recursive CTE to implement the split function.
Keep in mind that the OPTION(MAXRECURSION 0) does not go inside of the table valued function, be instead it goes in the query that calls the function.
GO CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(8000)) RETURNS table AS RETURN ( WITH splitter_cte AS ( SELECT CHARINDEX(@sep, @s) as pos, 0 as lastPos UNION ALL SELECT CHARINDEX(@sep, @s, pos + 1), pos FROM splitter_cte WHERE pos > 0 ) SELECT SUBSTRING(@s, lastPos + 1, case when pos = 0 then 80000 else pos - lastPos -1 end) as chunk FROM splitter_cte ) GO SELECT * FROM dbo.Split(' ', 'the quick brown dog jumped over the lazy fox') OPTION(MAXRECURSION 0);
which produced the following output.
chunk --------------------- the quick brown dog jumped over the lazy fox (9 row(s) affected)
There are programming tasks that can be accomplished easily with a Recursive Common Table Expression.