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 | |
SQL Server 2017 (14.x) and later has a native CONCAT_WS function.
For older versions, we can use a couple of tricks:
NULL values: COALESCE()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.
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 foo
ORDER 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 ALL
SELECT b UNION ALL
SELECT c UNION ALL
SELECT 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 bar
FROM foo
ORDER BY id;
For SQL Server 2012 you can simplify the accepted answer by replacing plethora of COALESCE with single CONCAT:
WITH tests(a, b, c, d) AS (
SELECT NULL, NULL, NULL, NULL UNION
SELECT NULL, NULL, NULL, 'd' UNION
SELECT NULL, NULL, 'c', NULL UNION
SELECT NULL, NULL, 'c', 'd' UNION
SELECT NULL, 'b', NULL, NULL UNION
SELECT NULL, 'b', NULL, 'd' UNION
SELECT NULL, 'b', 'c', NULL UNION
SELECT NULL, 'b', 'c', 'd' UNION
SELECT 'a', NULL, NULL, NULL UNION
SELECT 'a', NULL, NULL, 'd' UNION
SELECT 'a', NULL, 'c', NULL UNION
SELECT 'a', NULL, 'c', 'd' UNION
SELECT 'a', 'b', NULL, NULL UNION
SELECT 'a', 'b', NULL, 'd' UNION
SELECT 'a', 'b', 'c', NULL UNION
SELECT 'a', 'b', 'c', 'd'
)
SELECT a, b, c, d,
STUFF(CONCAT(
'; ' + a,
'; ' + b,
'; ' + c,
'; ' + d
), 1, 2, '') AS cat
FROM tests
a | b | c | d | cat
-----+------+------+------+-----------
NULL | NULL | NULL | NULL | NULL
NULL | NULL | NULL | d | d
NULL | NULL | c | NULL | c
NULL | NULL | c | d | c; d
NULL | b | NULL | NULL | b
NULL | b | NULL | d | b; d
NULL | b | c | NULL | b; c
NULL | b | c | d | b; c; d
a | NULL | NULL | NULL | a
a | NULL | NULL | d | a; d
a | NULL | c | NULL | a; c
a | NULL | c | d | a; c; d
a | b | NULL | NULL | a; b
a | b | NULL | d | a; b; d
a | b | c | NULL | a; b; c
a | b | c | d | a; b; c; d
I do it with FOR XML PATH.
You can use a union (UNION ALL) instead of VALUES; this has the added value that it still works on SQL-Server 2005 (we still have to support it in our company), and you can remove NULL values.
DECLARE @in_SearchTerm1 nvarchar(100)
DECLARE @in_SearchTerm2 nvarchar(100)
DECLARE @in_SearchTerm3 nvarchar(100)
DECLARE @in_SearchTerm4 nvarchar(100)
SET @in_SearchTerm1 = N'a'
SET @in_SearchTerm2 = N''
SET @in_SearchTerm3 = N'c'
SET @in_SearchTerm4 = N''
SELECT
COALESCE
(
STUFF
(
(
SELECT ' / ' + RPT_SearchTerm AS [text()]
FROM
(
SELECT NULLIF(@in_SearchTerm1, N'') AS RPT_SearchTerm, 1 AS RPT_Sort
UNION ALL SELECT NULLIF(@in_SearchTerm2, N'') AS RPT_SearchTerm, 2 AS RPT_Sort
UNION ALL SELECT NULLIF(@in_SearchTerm3, N'') AS RPT_SearchTerm, 3 AS RPT_Sort
UNION ALL SELECT NULLIF(@in_SearchTerm4, N'') AS RPT_SearchTerm, 4 AS RPT_Sort
) AS tempT
WHERE RPT_SearchTerm IS NOT NULL
ORDER BY RPT_Sort
FOR XML PATH(N''), TYPE
).value('.', 'nvarchar(MAX)')
,1
,3
,N''
)
,N''
) AS RPT_SearchTerms
Note the use of nvarchar - stop using varchar already.
You also have to order it, in order to preserve sequence.
So what does this do:
Goal:
Take the 4 search terms inputed in 4 separate filters in a report.
Display these 4 search terms in the report concatenated by ' / '.
There should not be a ' / / ' if a search term is empty.
It should be displayed in sequence, that is term1/term2/term3/term4, and not e.g. term4/term2/term3/term1.
How to:
So you take the 4 search terms into a union, and add a sort value to preserve order .
You select the search terms and the separator from the union (separatur + null = null)
SELECT ' / ' + RPT_SearchTerm
FROM (UNION OF SEARCH TEMRS) AS tempT
Order it by RPT_Sort
Now select all the values (separater + text) in tempT into one XML file (FOR XML), where all values are XML-elements with an empty-tagname (PATH(N''), and select the values XML-text (AS [text()]) (aka element.innerXML).
get the result of that as XML-element (TYPE) and retrieve the innerText string of that XML-element (.value('.', 'nvarchar(MAX)') ) (aka XML-decode).
Finally, remove the leading ' / ' (STUFF(var, 1,3, N''))
This is in principle exactly the same as
CONCAT_WS(' / ', @in_SearchTerm1, @in_SearchTerm2, @in_SearchTerm3, @in_SearchTerm4)
Now add nullif,
CONCAT_WS(' / ', NULLIF(@in_SearchTerm1, '') , NULLIF(@in_SearchTerm2, ''), NULLIF(@in_SearchTerm3, ''), NULLIF(@in_SearchTerm4, ''))
and you're there.
This is how you're still able to do CONCAT_WS in SQL-Server ...
i know this is old post but i come the same issue.
i simply use CONCAT() function for this.
i have address lines saved in individual fields and i wanted to join all lines to create address.
i found that CONCAT() function can handle NULL and replaces it with empty string. also if anything plus NULL is NULL.
so i used regular CONCAT() function and add space at the end of each address line so if that line is NULL then combine output is null
SELECT
CONCAT(Address01 + ' ', Address02 + ' ', Address03 + ' ', Address04) AS Address
FROM myTable
