Using a CTE to Split a String Into Rows

Download PDF

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.


Common table expressions

Download my book for FREE when you sign up for my mailing list.

Receive your free copy today!


 

More from Stedman Solutions:

SteveStedman5
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!

2 Comments on “Using a CTE to Split a String Into Rows

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*