Tuesday, March 3, 2009

How To Split A Comma Delimited String

In one of my previous posts I wrote about “How to Create a Comma Delimited List”.  Now I’ll show you an example with reverse action, that is Splitting a Comma Delimited String or array() of values.

There can be different types of Delimiters also : like Comma ' , ', vertical bar ' | ', Single space or a Tab.

For example here is our Sample Table -

Id AllNames
1 A,B,C
2 A,B
3 X,Y,Z

And here is the expected output -

Id Names
1 A
1 B
1 C
2 A
2 B
3 X
3 Y
3 Z

Create Sample Data :

-- Create Table for  Sample Data
CREATE TABLE Test
(
ID INT,
AllNames VARCHAR(100)
)
GO
-- Load Sample Data
INSERT INTO test SELECT
1, 'A,B,C' UNION ALL SELECT
2, 'A,B'  UNION ALL SELECT
3, 'X,Y,Z'
GO

-- Verify the Sample Data
SELECT Id, AllNames
FROM Test

And here is the query for How to split a comma delimited string :

;WITH Cte AS

(
    SELECT
        id,
        CAST('<M>' + REPLACE( Allnames,  ',' , '</M><M>') + '</M>' AS XML) AS Names
    FROM Test
)
SELECT
    ID,
    Split.a.value('.', 'VARCHAR(100)') AS Names
FROM Cte
CROSS APPLY Names.nodes('/M') Split(a)

 

Acknowledgement : Well seriously don’t know.  Somewhere I came across this solution while answering / participating  on MSDN Sql Server Forums.  So credit goes to my fellow Moderators/answrers on MSDN Forums.

Actually whatever expertise I gained on T-SQL and Sql Server is by participating / answering to the posts on online forums.  I’m one of those freaks who hate to read books ;)

- Mangal Pardeshi

22 comments:

  1. nice solution, thanks for posting it to your site.

    ReplyDelete
  2. Very elegant solution - much better than creating temp tables and such.

    ReplyDelete
  3. Very Helpful. Saved alot time

    ReplyDelete
  4. Perfect! Best thing I have found. Thank you!

    ReplyDelete
  5. i need something similiar like that , string :1010101111
    it should tell the position of all the character in the string
    like
    1 1
    0 2
    1 3

    is it possible

    ReplyDelete
  6. you sir, are a genius. this is exactly what i needed. other solutions did not work as elegantly as this. thank you for posting.

    ReplyDelete
  7. yeah,this is absolutely good but it is helpful,also if you are add the purpose of function why are you using there....

    ReplyDelete
  8. Awesome Solution , Great

    ReplyDelete
  9. This solution is just really great, I've been searching for something like this in for a while, only one problem though i'm using sql 2000 and it doesn't use the with clause. Is there any equivalent for sql 2000

    ReplyDelete
    Replies
    1. if you are using SQL2000, you neither have XML...

      Delete
  10. if you are using SQL 2000, you neither have XML...

    ReplyDelete
  11. Thank you, this was a great help.

    ReplyDelete
  12. how do i use this for multiple columns
    i get the following

    Msg 1011, Level 16, State 1, Line 6
    The correlation name 'Split' is specified multiple times in a FROM clause.

    when i do this

    -- Create Table for Sample Data
    drop table test
    CREATE TABLE Test
    (ID INT,
    AllNames VARCHAR(100),
    lastnames VARCHAR(100))
    GO

    -- Load Sample Data
    INSERT INTO test SELECT
    1, 'A,B,C', '1,2,3' UNION ALL SELECT
    2, 'A,B' , '7,8,9' UNION ALL SELECT
    3, 'X,Y,Z', 't,u,v'
    GO

    -- Verify the Sample Data
    SELECT Id, AllNames, lastnames
    FROM Test

    ;WITH Cte AS
    (SELECT
    id,
    CAST('' + REPLACE( Allnames, ',' , '') + '' AS XML) AS allNames ,
    CAST('' + REPLACE( lastnames, ',' , '') + '' AS XML) AS lastNames
    FROM Test)
    SELECT
    ID,
    Split.a.value('.', 'VARCHAR(100)') AS allNames,
    split.b.value('.', 'VARCHAR(100)') AS lastNames
    FROM Cte
    CROSS APPLY allNames.nodes('/M') Split(a)
    CROSS APPLY lastNames.nodes('/M') Split(b)

    ReplyDelete
  13. I have no language for say thanks to you. Great man.

    It was very helpful for me many many thanks

    ReplyDelete
  14. How can i insert these split values to diffrent table

    ReplyDelete
    Replies
    1. Hi Akshamya,
      Sorry didn't get your question. If you want insert values a table you can do like this:

      ;WITH Cte AS

      (
      SELECT
      id,
      CAST('' + REPLACE( Allnames, ',' , '') + '' AS XML) AS Names
      FROM Test
      )
      INSERT INTO AnotherTable(id, names)
      SELECT
      ID,
      Split.a.value('.', 'VARCHAR(100)') AS Names
      FROM Cte
      CROSS APPLY Names.nodes('/M') Split(a)

      Delete
  15. Hi Akshamya,
    Sorry didn't get your question. If you want insert values a table you can do like this:

    ;WITH Cte AS

    (
    SELECT
    id,
    CAST('' + REPLACE( Allnames, ',' , '') + '' AS XML) AS Names
    FROM Test
    )
    INSERT INTO AnotherTable(id, names)
    SELECT
    ID,
    Split.a.value('.', 'VARCHAR(100)') AS Names
    FROM Cte
    CROSS APPLY Names.nodes('/M') Split(a)

    ReplyDelete
  16. Hi,

    This is a great solution. May you please explain it so that I can understand how it works.

    Thanks
    Thato

    ReplyDelete
  17. Hi,

    This is a solution I've been looking for. May you please tell me how it works

    ReplyDelete
  18. Hi,

    What does the "M" in the function stand for?

    ReplyDelete