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))
 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 *


two + 4 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Newsletter Signup

Stay informed of Database Corruption Challenge events, and other things happening at with my newsletter.
Newsletter signup form.

Stedman Solutions