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.

Tagged with: , , ,
2 comments on “Using a CTE to Split a String Into Rows
  1. Joe Mikula says:

    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

    • SteveStedman SteveStedman says:

      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 *

*

Time limit is exhausted. Please reload CAPTCHA.