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

Download PDF

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))
 WITH splitter_cte AS (
 SELECT CHARINDEX(@sep, @s) as pos,
        cast(0 as bigint) as lastPos,
        0 as LineNumber
 SELECT CHARINDEX(@sep, @s, pos + 1),
        cast(pos as bigint),
        LineNumber + 1 as LineNumber
 FROM splitter_cte
 WHERE pos > 0
 SELECT LineNumber,
                 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:

 FROM dbo.SplitWithLineNumber (' ',
          'the quick brown dog jumped over the lazy fox')


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

 FROM dbo.SplitWithLineNumber (',',

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

Posted in CTE, SQL 2008, SQL 2012 Tagged with: , , , , , ,

Leave a Reply

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


Time limit is exhausted. Please reload CAPTCHA.