CONCAT_WS() is one among the new string function introduced in SQL Server 2017 along with TRANSLATE and TRIM. CONCAT_WS() is similar to CONCAT() but with an additional feature of concatenating with separator. In this article we’ll see about using CONCAT_WS().
Using CONCAT_WS() in SQL Server 2017 and Higher
Using CONCAT_WS() is simple and straight forward. You have to specify the separator in the form of char, nchar, varchar or nchar as the first argument. Then you can pass on the variables to concatenate in the subsequent arguments. Here are a couple of simple examples with string variables and table columns. As you can see this function will ignore NULL.
/** Simple Example **/
SELECT CONCAT_WS(', ', 'Adam', 'Jane', 'Thomas')
SELECT CONCAT_WS(', ', 'Adam', NULL, 'Thomas')
/* Result */
Adam, Jane, Thomas
Adam, Thomas
/** Concatenating Table Columns **/
select CONCAT_WS(' - ', Employee_ID), Employee_Name) As Result from MTB_Table_A
/* Result */
------------------
A002 - William
A003 - Jacob
A004 - Tyler
A005 - Emma
A006 - Ryan
A007 - Ashley
A008 - Brayden
A009 - Olivia
A010 - Michael
A011 - Emily
(10 row(s) affected)
CONCAT_WS() equivalent in SQL Server 2016 and Lower
CONCAT_WS() is not available in SQL Server 2016 and lower versions. The workaround to get similar functionality in lower versions of SQL Server is by using a CONCAT() function along with COALESCE() and manually adding the separators. Here is an example.
SELECT
CONCAT(
COALESCE(CustomerName + ' - ', ''),
COALESCE(PhoneNumber + ' - ', ''),
COALESCE(PostalAddressLine1 + ' - ', ''),
COALESCE(PostalAddressLine2 + ' - ', ''),
COALESCE(PostalPostalCode, '')
) AS Result
FROM Sales.Customers
/* Result */
-------------------------------------------------------------------------
Wingtip Toys (Ruthsburg, MD) - (240) 555-0100 - PO Box 6713 - Shinville - 90451
Eric Torres - (307) 555-0100 - PO Box 4858 - Sandhuville - 90218
Cosmina Vlad - (505) 555-0100 - PO Box 1954 - Gonzalesville - 90602
Bala Dixit - (209) 555-0100 - PO Box 8565 - Blahoville - 90676
Aleksandrs Riekstins - (605) 555-0100 - PO Box 6490 - Linnaville - 90797
Ratan Poddar - (907) 555-0100 - PO Box 6237 - Shakibaville - 90457
Shi Tu - (307) 555-0100 - PO Box 7197 - Nadarville - 90673
Gunnar Lohmus - (201) 555-0100 - PO Box 6430 - Malakarville - 90130
Jackson Kolios - (209) 555-0100 - PO Box 4028 - Lyville - 90693
Alena Kellnerova - (303) 555-0100 - PO Box 2343 - Radniaville - 90143
(10 row(s) affected)
Related Articles
- You may be interested in reading about the simple method to splitting delimited strings.
Reference
- Details about CONCAT_WS() at Microsoft Docs.
Hey thanks for a great webpage here. However I noticed an issue with your ‘2016 migration’.
If `PostalPostalCode` (i.e. the last value) is NULL then you end up with a trailing ‘-‘
Yes, the 2016 example is not equivalent to CONCAT_WS.