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

Years ago 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 > 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);

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!

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!