How To Split A Comma Delimited String

In one of my previous posts I wrote about “How to Create a Comma Delimited List”. Now I’ll show you an example with reverse action, that is Splitting a Comma Delimited String or array() of values.

There can be different types of Delimiters also : like Comma ' , ', vertical bar ' | ', Single space or a Tab.

For example here is our Sample Table -

Id	AllNames
1	A,B,C
2	A,B
3	X,Y,Z

And here is the expected output -

Id	Names
1	A
1	B
1	C
2	A
2	B
3	X
3	Y
3	Z

Create Sample Data :

-- Create Table for Sample Data CREATE TABLE Test ( ID INT, AllNames VARCHAR(100) ) GO -- Load Sample Data INSERT INTO test SELECT 1, 'A,B,C' UNION ALL SELECT 2, 'A,B' UNION ALL SELECT 3, 'X,Y,Z' GO -- Verify the Sample Data SELECT Id, AllNames FROM Test

And here is the query for How to split a comma delimited string :

;WITH Cte AS

(
SELECT
id,
CAST('' + REPLACE( Allnames, ',' , '') + '' AS XML) AS Names
FROM Test
)
SELECT
ID,
Split.a.value('.', 'VARCHAR(100)') AS Names
FROM Cte
CROSS APPLY Names.nodes('/M') Split(a)

Acknowledgement : Well seriously don’t know. Somewhere I came across this solution while answering/ participating on MSDN Sql Server Forums. So credit goes to my fellow Moderators/answrers on MSDN Forums.

Actually whatever expertise I gained on T-SQL and Sql Server is by participating / answering to the posts on online forums. I’m one of those freaks who hate to read books ;)