The best way to learn something is to revisit some of the older blogs and then look at finding a more efficient way to work on the same problem. Recently I was looking at the forums to find a question come up again and again around making a comma separated list. This is not new over this blog and if you search you will get few. Long time ago I have written below blogs to show the values stored in table as comma separate list – this is not a rehash of what was written before but a newer way to solve the problem again.
SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP
SQL SERVER – Comma Separated Values (CSV) from Table Column
Recently someone asked me a reverse of that. They were storing comma separated values in a column of table. This table was getting populated from some source via SSIS package. Here is the simplified version of the problem I was presented.
DECLARE @t TABLE ( EmployeeID INT, Certs VARCHAR(8000) ) INSERT @t VALUES (1,'B.E.,MCA, MCDBA, PGDCA'), (2,'M.Com.,B.Sc.'), (3,'M.Sc.,M.Tech.')
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:
SELECT EmployeeID,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS Certs
FROM
(
SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM @t
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
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.
Reference: Pinal Dave (https://blog.sqlauthority.com)
90 Comments. Leave new
What about the performance?
That’s a nice solution.
I had a really badly performing split function so re-wrote it as CLR, your’s is better as it’s all SQL code.
This is really a unique way of doing the same thing. I really liked it.
Hey, Dave,
You still used a function:
x.nodes(‘/XMLRoot/RowData’)m(n)
You meant no USER function.
Cheers!
Here is another solution, no function and no XML, however I used a system table to generate rows with numbers. I don’t say is faster or nicer or easier just different. And to be honest I adapted this:
DECLARE @t TABLE
(
EmployeeID INT,
Certs VARCHAR(8000)
)
INSERT @t VALUES (1,’B.E.,MCA, MCDBA, PGDCA’), (2,’M.Com.,B.Sc.’), (3,’M.Sc.,M.Tech.’), (4,’Psd,,IQ’)
SELECT
EmployeeID,
ltrim(rtrim(NullIf(SubString(‘,’ + Certs + ‘,’ , ID , CharIndex(‘,’ , ‘,’ + Certs + ‘,’ , ID) – ID) , ”))) AS Certs
FROM
(SELECT
ID = ROW_NUMBER() OVER (ORDER BY number)
FROM [master]..spt_values) Tally,
@t tbl
WHERE
ID 0 –remove this line to keep NULL rows(or empty value; ,,)
Unfortunately the code I posted was messed up by the website when submitted. Here I try again:
DECLARE @t TABLE
(
EmployeeID INT,
Certs VARCHAR(8000)
)
INSERT @t VALUES (1,’B.E.,MCA, MCDBA, PGDCA’), (2,’M.Com.,B.Sc.’), (3,’M.Sc.,M.Tech.’), (4,’Psd,,IQ’)
SELECT
EmployeeID,
ltrim(rtrim(NullIf(SubString(‘,’ + Certs + ‘,’ , ID , CharIndex(‘,’ , ‘,’ + Certs + ‘,’ , ID) – ID) , ”))) AS Certs
FROM
(SELECT
ID = ROW_NUMBER() OVER (ORDER BY number)
FROM [master]..spt_values) Tally,
@t tbl
WHERE
ID 0 –remove this line to keep NULL rows(or empty value; ,,)
Thanks TheSqlist.
Ok, no chance. You cannot post code here.
Hi Dave,
I get links to your article from Toolbox.
It is been a while that i touched any MSSQL code. I was wondering if there is an article that explains the different flavors of SQL (MS SQL, TSQL, Progue, MySQL, NoSQL, etc.) and summarizes the difference in syntax between them?
Thank you!
ilikecoding
ilikecoding – Thanks!
The key issue with this (and any creative use of XML capabilities) is that the source data needs to be XML escaped. A rogue less than, greater than it quote character (Unicode aside) will cause this query to fail.
Failing at some arbitrary future point based on valid user input data is not a good thing, so I steer clear of these approaches.
purposefulprocrastination – Agree with your comment.
One more method —
DECLARE @x AS XML=”
DECLARE @Param AS VARCHAR(100) = ‘Ind,Hary,Gu’
SET @x = CAST(‘‘+ REPLACE(@Param,’,’,’‘)+ ‘‘ AS XML)
SELECT t.value(‘.’, ‘VARCHAR(100)’) FROM @x.nodes(‘/A’) AS x(t)
There is no result from above query.
Updated–
DECLARE @x AS XML=”
DECLARE @Param AS VARCHAR(100) = ‘Ind,Hary,Gu’
SET @x = CAST(”+ REPLACE(@Param,’,’,”)+ ” AS XML)
SELECT t.value(‘.’, ‘VARCHAR(100)’) Value FROM @x.nodes(‘/r’) AS x(t)
Output-
Value
———
Ind
Hary
Gu
Pawan – Thanks for sharing
@Kumar,
That returns nothing.
When I am commenting wordpress actually remove the XML tags from the below statement.
SET @x = CAST(”+ REPLACE(@Param,’,’,”)+ ” AS XML)
Here in the first single quote we have add a starting tag then & then , after adding these tags try to run the query. This is working in my environment.
Regards,
Pawan
this thrown error like this XML parsing: line 1, character 181, semicolon expected
which one?
All I have a situation where I need to split the column to rows which is delimited using commas and semicolons. Please find the below sample data.
Data in Tables
Test1, Test2, Test3
Test4;Test5;Test6
Desired output
Test1
Test2
Test3
Test4
Test5
Test6
Is there any way that I can get this output in SQL other than using the XML Conversion, since the data has some special characters in this.
Hi Dave, if I have both comma and “and” as the separator, what’s the best way to do it? Thanks!
Thanks you so much, this sql was so helpful. :) keep up the good work!
I am glad it helped you parik !
How could I join on to the your query if the result was an integer joining on to that interger
join table p on p.Id= cert value
Thank you sir. It will be great if you explain the basic of XML in separate blog because i’m not able to understand how actual XML is processed.
Hi Pinal, I try to use above solution but it didn’t work for me. I am getting “XML parsing: line 1, character 55, illegal name character. Can you help me?
XML doesn’t support special character. I also faced same error.
Very nice solution! Worked great for my problem.