CONCAT_WS() for SQL Server

Question

How can I emulate MySQL's CONCAT_WS() function in SQL Server?

This function is similar to CONCAT() function in SQL Server 2012 except that it adds a separator between non-NULL items:

SELECT id, CONCAT_WS('; ', a, b, c, d) AS bar
FROM foo
ORDER BY id;

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

(MySQL Fiddle)

Answer 1

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 bar
FROM foo
ORDER 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.