Using a CTE to Split a String Into Rows
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.
Download my book for FREE when you sign up for my mailing list.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
How can this code be adjusted to split a 10 character string (i.e. AWHAHHHSAC) into 5 pairs, each under it’s own column:
HE SL1 SL2 SL3 SL4
—- —– —– —— ——
AW HA HH HS AC
With a constraint of always being a 10 character string, and 5 pairs, it might be easier to just use 5 substrings. It could be done with a recursive CTE, but that would be overkill for your request.