SQL 2017: Concat_WS - Another String Builder
Author: jason
Date: 2019-01-17 11:21:28
Category: Technical

SQL 2017 introduces more string building with CONCAT_WS, i.e. concatenate with string. This is tre cool.

https://docs.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-2017

Code


/*
CONCAT_WS: concatenate with string
NOTE: This function ignores NULL and does not send it out, unlike the original CONCAT
*/

DROP TABLE IF EXISTS #Temp;
CREATE TABLE #Temp ([Name] VARCHAR(25), [Description] VARCHAR(255), Quantity INT, SalesDate DATE);
INSERT #Temp VALUES ('Apple','Red', 10, '1/1/2019'), ('Apple','Green', NULL, '1/2/2019'), ('Plum','Purple', 5, '1/3/2019');

--Old way, put a comma between every value
--NOTE: In the results the empty space between the commas
SELECT CONCAT([Name], ',', [Description], ',', [Quantity], ',', [SalesDate]) AS OldConcat FROM #Temp;

--New way, put the comma in first, then put in all the values
--NOTE: In the results, the missing NULL value, so the comma pattern no longer matches
SELECT CONCAT_WS(',', [Name], [Description], [Quantity], [SalesDate]) AS NewConcat_Ws_No_Nulls FROM #Temp;

--If you need the blanks, you need to use ISNULL
SELECT CONCAT_WS(',', ISNULL([Name],''), ISNULL([Description],''), ISNULL([Quantity],''), ISNULL([SalesDate],'')) AS NewConcat_Ws_Nulls FROM #Temp;







jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009