Efficient way to string split using CTE

Question

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.

@Joel while I agree with you there are sometimes good reasons for knowing how to do this efficiently. Specifically scrubbing bad data coming from an integrated system where you must run the routine as a job on a regular cycle. I think the answer will be instructive and helpful to others. — NullRef, Jun 17 '11 at 05:28
@NullRef - Agree, and my comment has some hyperbole in it. That's why it's a comment, and not an answer. But I also think it's important to have that notice well-placed and well-voted for the unwary. — Joel Coehoorn, Jun 17 '11 at 05:38
@Joel I can't argue with that. I also can't argue with CSV=666! — NullRef, Jun 17 '11 at 05:44
How does using a CTE make T-SQL any better at string parsing? — Yuck, Jun 17 '11 at 12:45
Try the proc in [ this post](http://www.wisesoft.co.uk/scripts/t-sql_cte_split_string_function.aspx) where they use recursion to split. I think you can adapt it. This uses a CTE. — NullRef, Jun 17 '11 at 05:20

score 11 · Accepted Answer · edited May 23 '17 at 10:30

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?

It does not consider entries having one item only, like 'hello'. — mike, May 04 '20 at 10:36
This solution is way faster then the others I tried. I was able to fix the single entry issue using few conditional statements on main query in CTE `SELECT RowID, CASE WHEN CHARINDEX(',', Layout) > 0 THEN LEFT(Layout,CHARINDEX(',',Layout)-1) ELSE Layout END AS Part, CASE WHEN CHARINDEX(',', Layout)>0 THEN RIGHT(Layout,LEN(Layout)-CHARINDEX(',',Layout)) ELSE NULL END AS Remainder FROM @YourTable WHERE Layout IS NOT NULL AND (CHARINDEX(@separator,Layout) = 0 OR CHARINDEX(',',Layout)>0)` — vendettamit, Feb 22 '23 at 15:24

Arun Prasad E S · Answer 2 · 2021-07-23T15:41:15.777

From NullRef's Answer

Function without set operation will be faster according to my understanding of sql server

so this will be more efficient

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

score 2 · Answer 3 · answered Feb 12 '15 at 16:24

it's 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

Efficient way to string split using CTE

3 Answers3

Linked