New For SQL Server 2017: T-SQL Function CONCAT_WS

2018-03-29 0 Comments

Dave Mason SQL Server 2017 TSQL 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:

"Concatenates a variable number of arguments with a delimiter specified in the 1st argument. (CONCAT_WS indicates concatenate with separator.)"

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:

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

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.

0 comments: