concat_ws() in mssql

hey folks, im busy trying to make my script compatible with different types of databases

so, for mysql i use the function 'concat_ws()' in my sql query
but.. this function is not valid if u use mssql

so my question is, does anyone now a function in mssql that does the same as concat_ws() in mysql?Originally posted by bertwasbeer
hey folks, im busy trying to make my script compatible with different types of databases

so, for mysql i use the function 'concat_ws()' in my sql query
but.. this function is not valid if u use mssql

so my question is, does anyone now a function in mssql that does the same as concat_ws() in mysql?

Good question ;)
My question for you: What does the concat_ws() in mysql? I've never saw mysql.|||CONCAT_WS(separator, str1, str2,...)

CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator can be a string as well as the rest of the arguments. If the separator is NULL, the result will be NULL. The function will skip any NULL values after the separator argument. The separator will be added between the strings to be concatenated:

mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name");
-> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name");
-> 'First name,Last Name'|||Originally posted by bertwasbeer
CONCAT_WS(separator, str1, str2,...)

CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator can be a string as well as the rest of the arguments. If the separator is NULL, the result will be NULL. The function will skip any NULL values after the separator argument. The separator will be added between the strings to be concatenated:

mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name");
-> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name");
-> 'First name,Last Name'

I've never heard about function like this in MSSQL, may somebody else has. Anyway you could create User-Defined Functions for sql2000.|||What do you want to do with it?
If you are concatenating a column from selected rows you can

declare @.s varchar(8000)

select @.s = coalesce(@.s + ',') + fld
from tbl
where col2 = 'test'

This will concatenate all the values into @.s separated by a comma.