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.

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)

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

  • Thank you Dave!
    Even though the site said “… Without Using a Function” I really needed a proc. so, here it comes! Thank you again!

    Jacob

    CREATE PROCEDURE usp_Parse_Delimeted_Rows
    (@TableName as varchar(100),
    @Id_ColumnName as varchar(50),
    @ColumnName as Varchar(50),
    @Delimeter varchar(3))
    AS
    BEGIN

    DECLARE @SQL_String As VARCHAR(1000)

    SET @SQL_String = ‘SELECT ‘ + @Id_ColumnName +

    ‘ ,LTRIM(RTRIM(m.n.value(”.[1]”,” varchar(8000)”))) AS Parsed_Column
    FROM
    (
    SELECT ‘ + @Id_ColumnName + ‘ ,CAST(”” + REPLACE(‘ + @ColumnName + ‘,”’ + @Delimeter + ”’,””) + ”” AS XML) AS x
    FROM ‘ + @TableName + ‘
    )t
    CROSS APPLY x.nodes(”/XMLRoot/RowData”)m(n)’

    EXEC(@SQL_String)
    END
    GO

    Reply
  • If I understand what you need you can use the same code in sp. The Id column will give you what you need.
    CREATE PROCEDURE usp_Parse_Delimeted_Rows
    (@TableName as varchar(100),
    @Id_ColumnName as varchar(50),
    @ColumnName as Varchar(50),
    @Delimeter varchar(3))
    AS
    BEGIN

    DECLARE @SQL_String As VARCHAR(1000)

    SET @SQL_String = ‘SELECT ‘ + @Id_ColumnName +

    ‘ ,LTRIM(RTRIM(m.n.value(”.[1]”,” varchar(8000)”))) AS Parsed_Column
    FROM
    (
    SELECT ‘ + @Id_ColumnName + ‘ ,CAST(”” + REPLACE(‘ + @ColumnName + ‘,”’ + @Delimeter + ”’,””) + ”” AS XML) AS x
    FROM ‘ + @TableName + ‘
    )t
    CROSS APPLY x.nodes(”/XMLRoot/RowData”)m(n)’

    EXEC(@SQL_String)
    END
    GO

    Reply
  • Daniel Walters
    April 29, 2020 8:33 pm

    thank you so much for saving me time

    Reply
  • sandeep ghanwat
    September 10, 2020 9:37 am

    This is how data looks like in the table.

    CAN YOU PLEASE HELP ME , I WANT FOLLOWING OUTPUT OF QUERY ,

    DEPT. ID NAME
    1 SANDEEP,SAGAR
    2 SANJAY,SUHAS
    3 AJAY,AMAR

    Reply
  • Sitaram Parida
    April 21, 2021 12:49 pm

    Hi Dave,

    I am scenario like : in place of one field ,I have multiple fields where values are coming as comma separated and I wanted to the same way what your answered for One field .please can you suggest ,how we can achieve this in a single query?

    ex:

    id field 1 field 2 field 3

    1 test,test1 test2,test3 tes43,test5

    output:

    id field 1 field 2 field 3
    1 test tes2 test3
    1 test1 test3 test5

    Thanks in Advance,
    Sitaram

    Reply
  • Sitaram Parida
    April 21, 2021 1:41 pm

    typo :I have*

    Reply

Leave a Reply

Menu
Exit mobile version