CONCAT_WS() for SQL Server

We can use a couple of tricks:

• To skip NULL values: COALESCE()
• To avoid trailing separator: add it before every item, remove the first one afterwards with e.g. STUFF()

He's a working example:

CREATE TABLE foo (  id INT IDENTITY(1, 1) NOT NULL,  a VARCHAR(50),  b VARCHAR(50),  c VARCHAR(50),  d VARCHAR(50),  PRIMARY KEY (id));INSERT INTO foo (a, b, c, d) VALUES ('a', 'b', 'c', 'd');INSERT INTO foo (a, b, c, d) VALUES (NULL, 'b', NULL, 'd');INSERT INTO foo (a, b, c, d) VALUES ('a', NULL, NULL, 'd');INSERT INTO foo (a, b, c, d) VALUES (NULL, NULL, NULL, NULL);

SELECT id,STUFF(    COALESCE('; ' + a, '') +    COALESCE('; ' + b, '') +    COALESCE('; ' + c, '') +    COALESCE('; ' + d, ''),1, 2, '') AS barFROM fooORDER BY id

| ID | BAR        ||----|------------||  1 | a; b; c; d ||  2 | b; d       ||  3 | a; d       ||  4 | (null)     |

The purpose of STUFF(..., 1, 2, '') is to remove the initial separator (2 is the separator length in our case).

This should work on SQL Server 2005 (and possibly earlier versions).

Note: unlike the original CONCAT_WS(), our version returns NULL when all items are NULL. I honestly think it's a better choice but it should be easy to change anyway.

Another approach would be to use a FOR XML subquery like this:

SELECT  id,  bar = STUFF(    (      SELECT '; ' + v      FROM (VALUES (a), (b), (c), (d)) AS v (v)      FOR XML PATH (''), TYPE    ).value('.[1]', 'varchar(max)'),    1, 2, ''  )FROM fooORDER BY id;

On the one hand, this looks certainly more complicated than a series of COALESCE calls. On the other hand, this is closer to the prototype in that the delimiter is only specified once.

The syntax used requires at least SQL Server 2008+, but if the VALUES constructor is changed to

SELECT a UNION ALLSELECT b UNION ALLSELECT c UNION ALLSELECT d

the query will also run in SQL Server 2005.

Starting from SQL Server 2017 you could use built-in CONCAT_WS:

CONCAT_WS

Concatenates a variable number of arguments with a delimiter specified in the 1st argument. (CONCAT_WS indicates concatenate with separator.)

CONCAT_WS ( separator, argument1, argument1 [, argumentN]… ) 

Treatment of NULL values

CONCAT_WS ignores the SET CONCAT_NULL_YIELDS_NULL {ON|OFF} setting.

If all the arguments are null, an empty string of type varchar(1) is returned.

Null values are ignored during concatenation, and does not add the separator. This facilitates the common scenario of concatenating strings which often have blank values, such as a second address field. See example B.

If your scenario requires null values to be included with a separator, see example C using the ISNULL function.

So you could use your initial query:

SELECT id, CONCAT_WS('; ', a, b, c, d) AS barFROM fooORDER BY id;

db<>fiddle demo