How to split a comma delimited string in SQL Server

Biswajeet   August 31, 2014   No Comments on How to split a comma delimited string in SQL Server

In this article I’ll demonstrate how to split a comma delimited string in SQL Server.
There can be different types of delimiters like Comma ‘ , ‘, vertical bar ‘ | ‘, Single space or a Tab.
Here in below example i’m using Comma ‘ , ‘ delimiter.

Here is sample table:

download (1)

And here is the expected output:

download (2)

Create a sample table and insert data:

CREATE TABLE Test 
( 
   Id INT, 
   Country VARCHAR(200) 
) 
GO 
 
-- Insert Sample Data 
INSERT INTO Test SELECT
1, 'India, USA, UK' UNION ALL SELECT
2, 'Singapore, Dubai, Japan'  UNION ALL SELECT
3, 'Vietnam, South Africa, Ireland'
GO
 
-- Verify the Sample Data of Test Table
SELECT Id, Country FROM Test

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

;WITH cde AS
 
( 
    SELECT
        Id, 
        CAST('<m>' + REPLACE(Country,  ',' , '</m><m>') + '</m>' AS XML) AS Country
    FROM Test 
) 
SELECT
    Id, 
    Split.a.value('.', 'VARCHAR(100)') AS Country
FROM cde
CROSS APPLY Names.nodes('/M') Split(a)