SQL SERVER: How to split or convert delimited string into rows without using function

Introduction: In this article I am going to explain How to convert or split the delimited string (comma separated string) into table rows by converting them into xml and then parsing the xml to split them in rows. 

In previous articles I have explained How to Convert or split comma separated string into table rows in sql server and Get column values as comma separated list in sql server and  Split string from comma and get left and right part and Concatenate rows values as a comma separated string using for xml path and stuff and Separate integer and fractional part from decimal number

Implementation: Let’s create a table and add dummy data using the following script

CREATE TABLE tbItems

(

            Category VARCHAR(100),

            Items       VARCHAR(MAX)

)

GO

INSERT INTO tbItems(Category, Items)

VALUES 

('Dairy Products','Cheese,Milk,Yogurt'),

('Vegetables','Onion,Tomato'),

('Fruits','Apple,Banana,Orange')

View table data

GO

SELECT * FROM tbItems

Output will be:

Category	Items
Dairy Products	Cheese,Milk,Yogurt
Vegetables	Onion,Tomato
Fruits	Apple,Banana,Orange

Query to split delimited string 

GO

SELECT Category, col.value('.','VARCHAR(MAX)') AS Items 

FROM

(

            SELECT Category, CAST('<Items><item>' + REPLACE(Items, ',' , '</item><item>') + '</item></Items> ' AS XML) AS CategoryItems

            FROM tbItems

) AS tb

CROSS APPLY CategoryItems.nodes('/Items/item') tab(col)

Result will be:

Category	Items
Dairy Products	Cheese
Dairy Products	Milk
Dairy Products	Yogurt
Vegetables	Onion
Vegetables	Tomato
Fruits	Apple
Fruits	Banana
Fruits	Orange

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.

• Tweet
• Share
• Share
• Share

Related Post