SQL Yoga: SQL SERVER: Read values from Comma Separated variable

May 4, 2009

SQL SERVER: Read values from Comma Separated variable

As we have seen, How to generate Comma separated List in SQL. Today we know, how to get values from Comma separated column.

Many times developers asked, How can I read comma separated values from variable? There are many ways to get solution for this.

Lets discuss about the best way, I think so. We can use XML to read values from comma separated values. XML made our life easy.

Example:

I have one procedure which has one parameter VARCHAR(100), which might contains value like '1,5,6,20'. What I need to do is: I need to update rows contains these ID(1,5,6,20). So We need to make query which will update status of these IDs.

Solution:
I converted this VARCHAR Variable to XML by following way:
SET @xmlIDs =    '<IDs>
<ID>'
+ REPLACE(@str, ',', '</ID><ID>') + '</ID>' +
'</IDs>'



So this statement will generate XML from VARCHAR value as follows:




<IDs>
<ID>1</ID>
<ID>6</ID>
<ID>7</ID>
<ID>8</ID>
<ID>20</ID>
</IDs>



So, Now this is the XML, which can be easily read with SQL SERVER 2005 as:




SELECT x.v.value('.','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)



This will give me result set as: ( as separate table)



image



So that’s it, Now I can easily use this result set in my query, to update the rows accordingly.



So my Procedure looks like:




CREATE PROC Test_ReadValuesFromCommaSeparatedVariable
@str VARCHAR(100)
AS

DECLARE @XmlIDs XML

SET @xmlIDs = '<IDs>
<ID>'
+ REPLACE(@str, ',', '</ID><ID>') + '</ID>' +
'</IDs>'

UPDATE TableName
SET Flag = 1
WHERE ID IN(
SELECT x.v.value('.','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)
)



So, it is very easy to read values from Comma separated value.



Let me know if it helps you.

6 comments :

  1. Hey Tejas,

    Its nice work done by you, its very helpful topic for people.

    thanks & keep going...

    Keyur Patel

    ReplyDelete
  2. Thank you for this quick and easy method to search with a list! Very helpful!

    ReplyDelete
  3. Hi..

    This is nice..but i needed different output...not like this

    ReplyDelete
  4. Hi Arfan,

    Please find the same query for your expected result mentioned on: http://tejasnshah.wordpress.com/2009/04/08/sql-server-sql-query-to-find-table-dependencies/

    DECLARE @SQLYOga VARCHAR(MAX), @xmlIDs XML
    SELECT @SQLYoga = 'a1 - 2 Nos, a2 - 530 Nos, a3 - 2 Boxes'
    SET @xmlIDs = '
    ' + REPLACE(@SQLYOga, ',', '') + '' +
    ''


    SELECT x.v.value('.','VARCHAR(100)')
    FROM @xmlIDs.nodes('/IDs/ID') x(v)

    ReplyDelete
  5. Hi,
    Still i am not able to get my expected result...i having a stored table and having data in a field as

    a1- 2nos, a2 -230nos, a3-5nos in a column..i need this column data as
    a1 - 2nos
    a2 - 230nos
    a3 - 5nos

    Thanks in advance

    ReplyDelete
  6. Hi Arfaan,

    Can you please send me your query?

    Thanks,
    Tejas
    SQLYoga.com

    ReplyDelete