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
Hi Pinal Dave,
Great example!
Do you have any suggestion on how to “catch” the “split node” you are fetching?
E.g. in your example
EmployeeID, Certs, NodeCount
1 “B.E.” 1
1 “MCA” 2
1 “MCDBA” 3
1 “PGDCA” 4
2 “M.Com.” 1
2 “B.Sc.” 2
3 “M.Sc.” 1
3 “M.Tech” 2
Hi Dave, Here’s an answer on Stack-Overflow how to overcome the escaping issue in case of illegal characters:
Great. Thanks for sharing it.
Can you please report the link? It’s missing. thanks.
Hey Pinal,
I’m trying to adapt the above code to a special issue where I need to both delineate a comma delineated string, as well as pull out a quantity which is denoted by *n. Example: Item1*4, Item2, Item3. How can I adapt your above solution so that I get a row for each item, but then also parse the quantity for the same row?
It won’t work for you. How would you know which once is separator. Bad design.
i have a #table with multiple rows out of which need to extract one row which will have
c1, c2, c3, c4, c5
m , q, null, g, a
extracted one row and another table with value #t2 as below (ID, val1, Val2, ColName)
r1, m,n, c1
r2, q, p, c2
r3, r, s, c3
r1, z, y, c1
r2, a, b, c5
r3, g,h, c4
now i need to check for c1 c2, c3 , c4, c5 from #table one and get the values updated from #t2
output
c1, c2, c3, c4, c5
n , p, null, h, b
looks like you need to use dynamic query. I would suggest to post this in MSDN or StackOverFlow forums. There are experts who can help you better than me.
Nice work. But I get error on some point
XML parsing: line 1, character 81, semicolon expected
Half result returned is
1 | John Martin **09/21/52**
2 | Blank
i am using SQL 2012 so I cannot use string split function of 2016. My problem is I have to split out users comments into rows attached with Id.
example of comment in one of comment column is :
** ABC ** 05/30/2014 09:51:24 AM** Recieved Email dated Fri 5/30/2014 9:48 AM
** Mike Expert ** 05/24/2013 03:01:59 PM** Grouped recieved items
** Micky Donald ** 05/23/2013 01:26:36 PM** Changed customers according request
** ABC ** 04/10/2013 09:13:50 AM** PLEASE NOTE, XXX
This entire comment is related to 1 Id. 1 above. Now i need to split this one line into multiple rows.
Please help or suggest?
Hi Pinal,
I have the same questions as MD. Capturing the node would be really useful, do you know how?
Thanks
I have no idea. if yo find a solution, please share to help others.
SELECT EmployeeID, Split.a.value(‘.’, ‘VARCHAR(8000)’) AS Data, row_number() over (partition by convert(varchar,EmployeeID) order by a)
FROM
(
SELECT EmployeeID, CAST(” + replace(Certs, ‘,’, ”) + ” AS XML) AS Data
FROM @t
) AS A CROSS APPLY Data.nodes (‘/M’) AS Split(a)
Hi there!
This seems like it will be a perfect solution to what I am trying to solve. However, do you Pinal or anyone have any suggestions on an issue I am having with using this? The query completed with errors after it hit something it didn’t like.
Here’s the error that was thrown:
Msg 9455, Level 16, State 1, Line 1
XML parsing: line 1, character 39, illegal qualified name character
Any ideas on how to get around this?
Here’s how I adapted the code for my specific purpose:
SELECT ProductFormNbr,
LTRIM(RTRIM(m.n.value(‘.[1]’,’varchar(8000)’))) AS Codes
FROM
(
SELECT ProductFormNbr,CAST(” + REPLACE(Codes,’,’,”) + ” AS XML) AS x
FROM [dbo].[vwProductDetail]
)t
CROSS APPLY x.nodes(‘/XMLRoot/RowData’)m(n)
thanks it works fine
Hi Pinal ,
Which method is better
1 > By using function or
2 > By using this xml types
I have tested both with small data and looks similar do you have any idea?
I don’t have done any benchmark testing.
Thanks for posting this example, this was the perfect solution for the scenario i came across at work.
Thanks for sharing.
Hi Pinal, Is there any way to get the Split functionality in Azure SQL Dataware house (String_Split, UDF, XMLs are not supported ) and would like to use it on the fly JOINs multiple places in Single procedure.
This blog is a few years old, but just wanted to pipe in that the solutions works great and helped me out. Thanks for sharing. Your solution definitely has “legs”, since people are clearly still using it.
Thanks Jason.
Hi, I have Data like EmpName: A,B,C and Desg: D,E,F so I need an output like
empname desg
A D
B E
C F
Thanks Pinal, It works form me..
Perfect. Thanks @Pankaj
How to apply the same logic on multiple comma separated columns in the same table?
Awesome code, saved me!
Thanks!
Hi All,
I have a different scenario. I have an address as below,
22, Kings court, Austin, Texas.
I want a stored procedure to format the string this way,
22, Kings court,
Austin,
Texas.
I want the newline to start with the second comma.
Kind regards,
Hi Pinal, can you share the solution for this query ?
Query : Hi, I have Data like EmpName: A,B,C and Desg: D,E,F so I need an output like
empname desg
A D
B E
C F
I have a one Query like input column
input
id Column2Name
123 aa bbb ffff vvvvv eeeeee rrrrrr
I want output like
id column2
123 aa
123 bbb
123 ffff
123 vvvvv
123 eeeee
123 rrrrrrrr
if know any one suggestion me…..
Thanks !