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.
I have created one procedure which has one parameter VARCHAR(100). This procedure contains the parameter having value like '1,5,6,20'. The requirement is: Update the records having ID = 1,5,6,20 (Any of them). This is the one way that we can prepare the SQL query.
Convert VARCHAR variable to XML as follows:
SET @xmlIDs = '
' ' + REPLACE(@str, ',', ' ') + '' + '
So this statement will generate XML from VARCHAR value as follows:
1 6 7 8 20
So, Now this is the XML, which can be easily read in SQL SERVER (2005 and above) as:
SELECT x.v.value('.','INT') FROM @xmlIDs.nodes('/IDs/ID') x(v)
This will give me result set as: ( as separate table)
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 = '
' UPDATE TableName SET Flag = 1 WHERE ID IN( SELECT x.v.value('.','INT') FROM @xmlIDs.nodes('/IDs/ID') x(v) ) ' + REPLACE(@str, ',', ' ') + '' + '
So, it is very easy to read values from Comma separated value.
Let me know if it helps you.