What is SQL Server function to concatenate with separator

mysql, sql-server

Solution

There is no real equivalent (even the referenced answer is not exactly equivalent). You would normally add the separators between each value:

select A + ',' + B + . . .

One primary difference is that `concat_ws()` will skip NULL arguments, but `+` will not. You could emulate this behavior with:

select stuff((coalesce(',' + A, '') + coalesce(',' + B, '') + . . .
             ), 1, 1, '')

Of course, this doesn't convert the values to strings, as `concat_ws()` does. So, a closer version is something like:

select stuff((coalesce(',' + cast(A as varchar(255)), '') +
              coalesce(',' + cast(B as varchar(255)), '') +
              . . .
             ), 1, 1, '')

You can apply the same fixes to the referenced answer:

SELECT id,
       STUFF((SELECT ';' + cast(v as varchar(255))
              FROM (VALUES (a), (b), (c), (d)) AS v (v)
              WHERE v is not null
              FOR XML PATH (''), TYPE
             ).value('.[1]', 'varchar(max)'),
             1, 1, ''
            )
FROM foo
ORDER BY id;

Problem

I am looking for SQL Server equivalent of MySQL concat_ws to concatenate strings with separator. I could not find one, is there a single function in SQL server to achieve this task?

Original source

Related problems