SQL SERVER: HOW TO SPLIT OR CONVERT DELIMITED STRING INTO ROWS WITHOUT USING FUNCTION

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:

Query to split delimited string

GO
SELECT Category, col.value(‘.’,‘VARCHAR(MAX)’) AS Items
FROM
(
SELECT Category, CAST(‘’ + REPLACE(Items, ‘,’ , ‘’) + ‘ ’ AS XML) AS CategoryItems
FROM tbItems
) AS tb
CROSS APPLY CategoryItems.nodes(’/Items/item’) tab(col)

Result will be: