Efficient way to split lines using CTE

Question

I have a table that looks like

ID Layout 1 hello,world,welcome,to,tsql 2 welcome,to,stackoverflow 

The desired result should be

 Id Splitdata 1 hello 1 world 1 welcome 1 to 1 tsql 2 welcome 2 to 2 stackoverflow 

I did it on request below

 Declare @t TABLE( ID INT IDENTITY PRIMARY KEY, Layout VARCHAR(MAX) ) INSERT INTO @t(Layout) SELECT 'hello,world,welcome,to,tsql' union all SELECT 'welcome,to,stackoverflow' --SELECT * FROM @t ;With cte AS( select F1.id ,O.splitdata from ( select *, cast('<X>'+replace(F.Layout,',','</X><X>')+'</X>' as XML) as xmlfilter from @t F )F1 cross apply ( select fdata.D.value('.','varchar(MAX)') as splitdata from f1.xmlfilter.nodes('X') as fdata(D)) O ) select * from cte 

But in terms of performance, this is very bad. I am looking for a more efficient query, but only use CTE.

Answer 1

It looks like you are outdated when using CTE, so try the following:

 DECLARE @YourTable table (RowID int, Layout varchar(200)) INSERT @YourTable VALUES (1,'hello,world,welcome,to,tsql') INSERT @YourTable VALUES (2,'welcome,to,stackoverflow') ;WITH SplitSting AS ( SELECT RowID,LEFT(Layout,CHARINDEX(',',Layout)-1) AS Part ,RIGHT(Layout,LEN(Layout)-CHARINDEX(',',Layout)) AS Remainder FROM @YourTable WHERE Layout IS NOT NULL AND CHARINDEX(',',Layout)>0 UNION ALL SELECT RowID,LEFT(Remainder,CHARINDEX(',',Remainder)-1) ,RIGHT(Remainder,LEN(Remainder)-CHARINDEX(',',Remainder)) FROM SplitSting WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)>0 UNION ALL SELECT RowID,Remainder,null FROM SplitSting WHERE Remainder IS NOT NULL AND CHARINDEX(',',Remainder)=0 ) SELECT * FROM SplitSting ORDER BY RowID 

OUTPUT:

 RowID Part ----------- ----------------------- 1 hello 1 world 1 welcome 1 to 1 tsql 2 welcome 2 to 2 stackoverflow (8 row(s) affected) 

here's a great article on row splitting in SQL Server: “Arrays and lists in SQL Server 2005 and Beyond when table value parameters are not cut” by Erland Sommargog

EDIT here is another version (but you need a table of numbers) returns the same results as above:

 ;WITH SplitValues AS ( SELECT RowID,ListValue FROM (SELECT RowID, LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(',', List2, number+1)-number - 1))) AS ListValue FROM ( SELECT RowID, ',' + Layout + ',' AS List2 FROM @YourTable ) AS dt INNER JOIN Numbers n ON n.Number < LEN(dt.List2) WHERE SUBSTRING(List2, number, 1) = ',' ) dt2 WHERE ListValue IS NOT NULL AND ListValue!='' ) SELECT * FROM SplitValues 

see number table: What is the best way to create and populate a number table?

Answer 2

this is my best solution using CTE:

 DECLARE @Char VARCHAR(MAX) = '10||3112||||aaaa||' DECLARE @Separador CHAR(2) = '||' ;WITH Entrada AS( SELECT CAST(1 AS Int) As Inicio, CHARINDEX(@Separador, @Char) As Fim UNION ALL SELECT CAST(Fim + LEN(@Separador) AS Int) As Inicio, CHARINDEX(@Separador, @Char, Fim + 1) As Fim FROM Entrada WHERE CHARINDEX(@Separador, @Char, Fim + 1) > 0 ) SELECT SUBSTRING(@Char, Inicio, Fim - Inicio) FROM Entrada WHERE (Fim - Inicio) > 0 

Answer 3

From NullRef answer

Feature without installation will be faster according to my understanding of SQL Server

therefore it will be very effective

 CREATE FUNCTION fnSplitString(@str nvarchar(max),@sep nvarchar(max)) RETURNS TABLE AS RETURN WITH a AS( SELECT CAST(0 AS BIGINT) as idx1,CHARINDEX(@sep,@str) idx2 UNION ALL SELECT idx2+1,CHARINDEX(@sep,@str,idx2+1) FROM a WHERE idx2>0 ) SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as value FROM a