Split CSV string values

   Read OR Split CSV string values: Often users need to parse a CSV string in a Stored Procedure; and for this task they call some user defined function passing the CSV string as parameter. Output from the function will be a Table. But instead of calling a function, developer can make use of CTE and get the same output within Stored Procedure only, without calling any function.

Let's say, we have a CSV string as "a,b,c,d,e"
And want to Parse this without calling a function, then do as below :

 

DECLARE @str VARCHAR(1000)

SELECT @str = 'a,b,c,d,e'



;WITH CTE(i,j)

AS

(

SELECT i=1, j=CHARINDEX(',',@str +',')



UNION ALL



SELECT i=j+1, j=CHARINDEX(',',@str +',',j+1) FROM CTE

WHERE CHARINDEX(',',@str +',',j+1) <> 0



)

SELECT SUBSTRING(@str ,i,j-i)

FROM CTE 

And the output of the above code is :

a

b

c

d

e

So here you are done; you can now make the result as derived table and make joins or any other functionalities you like ......

 Reference: Govind Badkur(http://sqlserver20.blogspot.com)