1

After working on a different question here on SO, I stumbled across recursive CTEs which would on the surface seem a fairly easy way to solve the "Split a csv to table rows" problem.

I put this example together

DECLARE @InputString varchar(255) = 'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z'

SELECT @InputString = @InputString + ','
;
with MyCTE(x,y)
as 
(
    SELECT 
        x = SUBSTRING(@InputString,0,PATINDEX('%,%',@InputString)),
        y = SUBSTRING(@InputString,PATINDEX('%,%',@InputString)+1,LEN(@InputString))
    UNION ALL
        SELECT 
            x = SUBSTRING(y,0,PATINDEX('%,%',y)),
            y = SUBSTRING(y,PATINDEX('%,%',y)+1,LEN(y))
        FROM 
            MyCTE 
        WHERE
                SUBSTRING(y,PATINDEX('%,%',y)+1,LEN(y)) <> '' OR 
            SUBSTRING(y,0,PATINDEX('%,%',y)) <> ''
)
SELECT x FROM MyCTE
OPTION (MAXRECURSION 2000);
GO

Is this really a bad idea? What is the overhead in SQL for recursive queries like this, and what are the potential pitfalls for this kind of approach.

Incidentally, I'm thinking this idea/technique could probably be leveraged to solve this other question.

Community
  • 1
  • 1
Runonthespot
  • 875
  • 9
  • 17

2 Answers2

7

This one will work with empty strings

DECLARE @InputString VARCHAR(1000)
    SELECT @InputString = 'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,,1'

    SELECT SUBSTRING(',' + @InputString + ',', Number + 1,
    CHARINDEX(',', ',' + @InputString + ',', Number + 1) - Number -1)AS VALUE
    FROM master..spt_values
    WHERE type = 'p'
    AND Number <= LEN(',' + @InputString + ',') - 1
    AND SUBSTRING(',' + @InputString + ',', Number, 1) = ','
    GO

Also take a look at the comments here: Split string in SQL Server 2005+ CLR vs. T-SQL for some other ideas

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
  • Interesting as I guess that solution is basically forward recursion? Is there a more general solution that doesn't require master..spt_values (i.e. supports > 2047 items) ? – Runonthespot May 13 '11 at 14:28
  • Something with ROW_NUMBER() maybe? – Runonthespot May 13 '11 at 14:28
  • You can have your own number table, the reason I show spt_values is because every SQL Server installation has it – SQLMenace May 13 '11 at 14:35
  • Fair enough. I suppose Row_Number() over say 2 x sysobjects joined would give me as many numbers as I needed. I don't see any major difference in performance between this solution & mine (now ammended to terminate correctly), so still interested my question as to what the pitfalls of recursion here are? – Runonthespot May 13 '11 at 14:47
2

Though it's too late now and also the OP has an accepted answer, but still it's worth mentioning to read the article on Split Function in Sql Server using Set base approach where the author has shown many ways of achieving the same.

Niladri Biswas
  • 4,153
  • 2
  • 17
  • 24