Comma Delimited SQL string Need to separated

Question

I have this string that i am getting from .net application A,B,C,D,E,F,

I wanted to write a sql select statement like

set @string = 'A,B,C,D,E,F'

select * from tbl_test 
where tbl_test.code in (@string)

This wont work in t-SQL because it is using the @string as one string it is not separating the values. Is there any ways i can do this?

Answer 1

3 options

1. Use a regular expression to replace the "," with "','" so that it becomes a proper ('A','B'...) list
2. Convert the list to XML and then parse the XML in your SELECT
3. Write a SPLIT function to convert comma delimited lists to tables

Answer 2

Very frequently asked question! What you want is a table-valued function.

But don't reinvent the wheel by writing your own, I found dozens just by Googling sql split. Here's one from Microsoft:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=StringArrayInput

I used to use dynamic SQL for this, but that meant I had to dig up the code and copy it into each new app. Now I don't even have to think about it.

Answer 3

It think the easiest way to do it, will be, dynamic SQL generation:

// assuming select is a SqlCommand
string[] values = "A,B,C,D,E,F".Split(',');
StringBuilder query = new StringBuilder();
query.Append("select * from tbl_test where tbl_test.code in (");
int i = 0;
foreach (string value in values) {
    string paramName = "@p" + i++;
    query.Append(paramName);
    select.Parameters.AddWithValue(paramName, value);
}
query.Append(")");
select.CommandText = query.ToString();

// and then execute the select Command