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 came 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 &amp;gt; 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);

CTEtoSplitaStringIntoRowswithLineNumbers

If you want to split up a comma delimited list, you could do it like this.

SELECT *
 FROM dbo.SplitWithLineNumber (',',
          'this,is,my,comma,separated,list,it,has,several,items,split,up,by,commas')
OPTION(MAXRECURSION 0);

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

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

Receive your free copy today!