I have a .net code which i need to replicate as it is in sql server. Need a same user defined function as follows. As I am not that much expert in sql server could you please help me.
My .net function is as follows.
public static string GetCsv(params string[] list)
{
string comma = "";
string ret = "";
for (int i = 0; i < list.Length; i++)
{
if (!string.IsNullOrEmpty(list[i]))
{
ret += comma + list[i];
comma = ",";
}
}
return ret;
}
I have two answers:
- Pass a list of elements, as an array, to a function and concatenate them with "," delimiter
- Prepare a comma-deleted list of the values in a Column (i.e. from a Table in the database)
#2 is easy to do - if that is what you want?
#1 is not really something that you want to do in SQL. More normally you already have the Column Values in a Table in the database, and you want to concatenate them.
However, there is another scenario for #1:
1a. pass a delimited-list to a query and select all rows, in a table, that match any of those values.
For example, you want all Customers that have ID in the list (1234, 5678, 9012, ...)
- in that example you can pass a delimited list, or an array, to SQL and then use those values in a SQL statement.
Personally I find passing an array a nuisance to program, so we use a delimited-list in that situation.