New For SQL Server 2017: T-SQL Function CONCAT_WS

In the last post, I looked at a new T-SQL function for SQL Server 2017. Let's continue down that path and look at CONCAT_WS(), which is also new for SQL Server 2017. Here's the definition of the function from Microsoft Docs:

Here's an example where four string literals are concatenated with a pipe character as the delimiter/separator (output displayed as text):

SELECT CONCAT_WS(
 '|',
 'Param 1', 'Param 2', 'Param 3', 'Param 4'
) AS Concat_Expression

Concat_Expression
-------------------------------------
Param 1|Param 2|Param 3|Param 4

(1 row affected)

This is a really convenient function. It's nice not having to type single quotes, concatenation character(s), and the plus sign (+) over and over again. There's more to like, though. All of the function arguments are implicitly converted to string types. There's no need to CAST them to (N)VARCHAR or (N)CHAR. I told you it's convenient! Here's another example with database names and sizes on disk (output displayed as text):

--Database sizes on disk.
SELECT CONCAT_WS(
 ' : ',
 db_name(f.database_id), 
 SUM(f.size) / 128 / 1024.0 
) AS [DBName: Size_GB]
FROM master.sys.master_files f
GROUP BY f.database_id
GO

DBName: Size_GB
--------------------------------------
master : 0.005859
tempdb : 0.093750
model : 0.015625
msdb : 0.038085
DWDiagnostics : 1.046875
DWConfiguration : 0.015625
DWQueue : 0.015625
JustElectrons : 1.791015
pubs : 0.004882
Rtesting : 0.062500
AdventureWorks : 0.202148
WideWorldImporters : 3.160156

(12 rows affected)

NULL Values

Let's look at one last example that showcases another great aspect of CONCAT_WS: NULL values are ignored during concatenation. This is terrific when working with addresses. Consider this table of address data:

Addresses are kind of a pain in the ass--at least the US-based addresses I'm accustomed to working with. Since they're commonly stored in multiple free-form columns, which may or may not have data, it's not always easy to determine the number of "address lines" there are for each address. With CONCAT_WS, data from multiple columns can be joined together with a carriage return/line feed and the NULL values are ignored (the separator won't be added either). Here's a query that returns the addresses from the table above (output displayed as text):

SELECT CONCAT_WS(
 CHAR(13) + CHAR(10), --separator
 te.OwnerName1,
 te.OwnerName2,
 te.Address1,
 te.Address2,
 te.Address3,
 te.City + ', ' + te.StateCode + ' ' + te.ZipCode,
 REPLICATE('-', 30)  
)
FROM ##TempAddress te
GO

ORANGE CO EXPY AUTHORITY
4974 ORL TOWER RD
ORLANDO, FL 328071684
------------------------------
ORANGE COUNTY BCC
CITY OF ORLANDO
PO BOX 1393
ORLANDO, FL 328021393
------------------------------
ORLANDO UTILITIES COMMISSION
CITY OF ORLANDO
500 S ORANGE AVE
SUITE 550
ORLANDO, FL 328013708
------------------------------
CITY OF ORLANDO
ORLANDO UTILITIES COMM
C/O AT AND T MOBILITY
ATTN: NREA-TAX
5405 WINDWARD PKWY
ALPHARETTA, GA 300043894
------------------------------

(4 rows affected)

CONCAT_WS handles my data, whether there are 3, 4, 5, or 6 address lines. There are no unwanted blank lines and no unintended concatenation. One important note, though: CONCAT_WS ignores NULLs, but it does not ignore empty strings. If there are NOT NULL columns populated with an empty string, consider using NULLIF to get the desired results.