aditi aditi - 17 days ago 7
SQL Question

Efficient way to string split using CTE

I have a table that looks like

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


The desired output should be

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


I have done this by the below query

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 performance wise it is very bad. I am looking for a more efficient query but using CTE only.

Please help

Thanks

KM. KM.
Answer

You seem dead set on using a CTE, so try this:

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 is an excellent article on splitting strings in SQL Server: "Arrays and Lists in SQL Server 2005 and Beyond, When Table Value Parameters Do Not Cut it" by Erland Sommarskog

EDIT here's another version (but you need a numbers table) returns 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 here for a numbers table: What is the best way to create and populate a numbers table?

Comments