CONCAT_WS() for SQL Server

by

SQL Server 2017 (14.x) and later has a native CONCAT_WS function.

For older versions, 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.

More Related Contents:

  1. How do I UPDATE from a SELECT in SQL Server?
  2. How to return only the Date from a SQL Server DateTime datatype
  3. How to calculate age (in years) based on Date of Birth and getDate()
  4. CROSS JOIN vs INNER JOIN in SQL
  5. Referring to a Column Alias in a WHERE Clause
  6. Convert Month Number to Month Name Function in SQL
  7. Function vs. Stored Procedure in SQL Server
  8. Multiple INSERT statements vs. single INSERT with multiple VALUES
  9. Limit on the WHERE col IN (…) condition
  10. Removing leading zeroes from a field in a SQL statement
  11. Generate a resultset of incrementing dates in TSQL
  12. converting Epoch timestamp to sql server(human readable format)
  13. Getting result of dynamic SQL into a variable for sql-server
  14. How to get difference between two rows for a column field?
  15. Better techniques for trimming leading zeros in SQL Server?
  16. How do I split a delimited string so I can access individual items?
  17. What’s the best way to select the minimum value from several columns?
  18. Counting DISTINCT over multiple columns
  19. SQL Server SELECT into existing table
  20. Why use Select Top 100 Percent?
  21. How to check if a stored procedure exists before creating it
  22. Correct use of transactions in SQL Server
  23. How do I prevent a database trigger from recursing?
  24. Aggregate function in an SQL update query?
  25. Executing SQL Server Agent Job from a stored procedure and returning job result
  26. how does SELECT TOP works when no order by is specified?
  27. is it possible to select EXISTS directly as a bit?
  28. Convert date to YYYYMM format
  29. Conversion failed when converting the varchar value ‘simple, ‘ to data type int
  30. Date serial in SQL?

Leave a Comment