What is SQL Server function to concatenate with separator

Question

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?

Answer 1

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;