Using a CTE to Split a String Into Rows with Line Numbers

Last year while working on my CTE presentation for a SQL Saturday I added a blog post called “Using a CTE to Split a String Into Rows“, and since that posting I have used it many times.   But as things go in development, eventually there is a need to do something more.

Doing some complex string building to create files lately I cam across the need to use a CTE to split strings into rows but to also include line numbers.  To accomplish that I first started with the same function that I used last year, but renamed it, and added a second output column called LineNumber, here is what it looked like:

CREATE FUNCTION dbo.SplitWithLineNumber (@sep char(1), @s varchar(max))
RETURNS table
AS
RETURN (
 WITH splitter_cte AS (
 SELECT CHARINDEX(@sep, @s) as pos,
        cast(0 as bigint) as lastPos,
        0 as LineNumber
 UNION ALL
 SELECT CHARINDEX(@sep, @s, pos + 1),
        cast(pos as bigint),
        LineNumber + 1 as LineNumber
 FROM splitter_cte
 WHERE pos > 0
 )
 SELECT LineNumber,
        SUBSTRING(@s,
                 lastPos + 1,
                 case when pos = 0 then 2147483647
                      else pos - lastPos -1 end) as chunk
 FROM splitter_cte
 );

The things that have changed since the last post “Using a CTE to Split a String Into Rows“, are the following:

• I added another column to the output called Line Number.
• Modified the input to be varchar(max).
• Adjusted the case statement to accommodate varchar(max).
• Cast the positions as bigint’s in order to accomodate varchar(max).

So now when you run this on the original input, you get the following:

SELECT *
 FROM dbo.SplitWithLineNumber (' ',
          'the quick brown dog jumped over the lazy fox')
OPTION(MAXRECURSION 0);

Just a small change, but if you need to lave line numbers on the output, this will do it for you.