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.

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
SELECT EmployeeID,CAST('<XMLRoot><RowData>' + REPLACE(Certs,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   @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)

SQL Function, SQL Scripts, SQL Server
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,

    I’ve been struggling with a finding a good solution for this until I found this post. Thank you!

  • Really awesome solution, good one

  • Very useful thanks, but its taking long time to provide the output with high values. is there any option to reduce it.

  • Mark Burgess
    May 27, 2016 10:56 am

    Trying to make this work, but get an error in SSIS that I’m using an invalid character in the CAST Expression.

  • Mike Scalise
    May 27, 2016 7:51 pm


    How would this XML query look if there were two columns with an equal number of values whose values need to be split into rows?

    For example, if there were a YearEarned field in addition to a cert field.

    EmployeeID, Certs, YearsEarned
    1, “B.E.,MCA, MCDBA, PGDCA”, “2006, 2010, 2011, 2015”

    What I would want is:

    1, B.E., 2006
    1 MCA, 2010
    1, MCDBA, 2011,
    1, PGDCA, 2015




  • Satyajit Mohanty
    May 30, 2016 1:43 pm

    Hi Pinal,

    I have the same question as Mike, where more than one column is there with delimited values and how to make multiple columns to arrange their values row wise.
    Could you please help ?

  • Mike Scalise
    June 1, 2016 6:54 am

    Any thoughts, Pinal?

  • @Mike

    have you thought of using any of the code above in 2 parts, followed by inner join on EmpoyeeID?

    1, B.E.
    1, MCA
    1, MCDBA
    1, PGDCA

    1, 2006
    1, 2010
    1, 2011
    1, 2015

    • Mike Scalise
      June 3, 2016 7:48 pm

      That’s not a bad idea. I can give it a try. I was just trying to see if there was a way to do it in one query.

  • Hi This is Prakash, I need requirement like this.

    EmployeeID, Certs, YearsEarned
    1, “B.E.,MCA, MCDBA, PGDCA”, “2006, 2010, 2011, 2015”

    What I would want is: Single row with multiple lines in that column value

    1, B.E., 2006
    MCA, 2010
    MCDBA, 2011,
    PGDCA, 2015

    Can anybody help on this.

  • Oh this is perfect! Thanks!

  • Excellent, Very Thanks, was perfect solution to my problem, already that in company where job are not authorized create function in database.

    Sorry , my English is not good, I hope you understand the comment.
    Your page and post are helpful .


  • Nikhil Khokale
    August 24, 2016 3:31 pm

    Hi Pinal,
    Thanks for your XML code, it works in my case.
    But now i’m facing error while executing in one of the case where list is empty for some records. For further investigation i pass the record which has NULL value (AllBarCode) in where clause, it works there but while executing the same for all records it gives below error.

    Query : SELECT PCBGUID, ALLBarCode AS ALLBarCode_Actual, LTRIM(RTRIM(m.n.value(‘.[1]’, ‘varchar(8000)’))) AS ALLBarCode, Lot FROM (SELECT PCBGUID, ALLBarCode, CAST(” + REPLACE(ALLBarCode, ‘,’, ”) + ‘…

    Error Source: .Net SqlClient Data Provider
    Error Message: XML-Analyse: Zeile 1, Zeichen 19, ungültiges XML-Zeichen.

    Could you please let me know does something went wrong in this.

  • declare @str varchar(max) = ‘1,2,3,4,5,6’, @sql varchar(max)

    select @sql = ‘select * from (values’ + ‘(‘ + replace(@str,’,’,’),(‘) + ‘)) as T(ID)’

    exec (@sql)

  • Jeyakumar Narasingam
    October 4, 2016 11:52 am

    You may have “&” in your xml formed. Please replace them with ;amp. It should work.

  • kahnerrodriguez
    October 13, 2016 1:32 am

    I’m not clear what this part of the code is doing, if anyone cares to explain.
    m.n.value(‘.[1]’,’varchar(8000)’) . What is m.n.value()

  • Amazing Stuff. Worked a wonder for me,

  • can someone please explain how its working.
    SELECT EmployeeID,
    LTRIM(RTRIM(m.n.value(‘.[1]’,’varchar(8000)’))) AS Certs
    SELECT EmployeeID,CAST(” + REPLACE(Certs,’,’,”) + ” AS XML) AS x
    FROM @t
    CROSS APPLY x.nodes(‘/XMLRoot/RowData’)m(n)

  • Hi I have requirement like this
    I have list of acceptable values separated by comma like True,False,Ok,Cancel
    I have to check provided input say True,Ok presnet in acceptable values or not if yes return 1 else 0

  • Hi Pinal ,

    I am new to sql , could you please suggest how the above query can be used if we have multiple columns having comma separated values.

    Thank you ,

  • Thank you for your help. This worked perfectly for me. I just had to remove the “EmployeeID” from the top of the select statement.


Leave a Reply