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.')

 

Solarwinds

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)

Solarwinds
, ,
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

87 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

Leave a Reply

Menu