SQL SERVER – Split Comma Separated List without Using a Function

Create table “tblTest” in sql server

This is how data looks like in the table

With the above data available, they want it split into individual values. Searching for possible solutions, here is the solution query I came up with:

DECLARE @temp TABLE(               ID INT,               Name Varchar(MAX) ) INSERT INTO @temp (ID, Name) SELECT ID, LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) As Name FROM ( SELECT ID, CAST('<XMLRoot><RowData>' + REPLACE(Name,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x FROM   tblTest) tblTest CROSS APPLY x.nodes('/XMLRoot/RowData')m(n) SELECT * FROM @temp WHERE Name = 'kinjal'

Output looks like below.

Hope this would help you in your project. Let me know if you are able to find a different solution to the same problem. The best way to learn is to learn from each other.