SQL SERVER – Split Comma Separated List Without Using a Function

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.

SQL SERVER - Split Comma Separated List Without Using a Function csv-split-01

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.

SQL SERVER - Split Comma Separated List Without Using a Function csv-split-02

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)

, ,
Previous Post
SQL SERVER – How to View the Dirty Pages In Memory of a Database?
Next Post
SQL SERVER – How to Identify a DB is Using Cross-Database Transactions?

Related Posts

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

    Reply
  • Hi Dave, Here’s an answer on Stack-Overflow how to overcome the escaping issue in case of illegal characters:

    Reply
  • 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?

    Reply
  • 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

    Reply
    • 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.

      Reply
  • Nice work. But I get error on some point
    XML parsing: line 1, character 81, semicolon expected

    Reply
    • Half result returned is

      1 | John Martin **09/21/52**
      2 | Blank

      Reply
      • 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

    Reply
    • I have no idea. if yo find a solution, please share to help others.

      Reply
      • 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)

    Reply
  • thanks it works fine

    Reply
  • 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?

    Reply
  • Thanks for posting this example, this was the perfect solution for the scenario i came across at work.

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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

    Reply
  • Thanks Pinal, It works form me..

    Reply
  • How to apply the same logic on multiple comma separated columns in the same table?

    Reply
  • Awesome code, saved me!
    Thanks!

    Reply
  • 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,

    Reply
  • 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

    Reply
  • 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…..

    Reply
  • Thanks !

    Reply

Leave a Reply

Menu