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);
If you want to split up a comma delimited list, you could do it like this.
SELECT * FROM dbo.SplitWithLineNumber (',', 'this,is,my,comma,seperated,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.