In my previous post on new functions in SQL Server 2017, I discussed about the STRING_AGG function. Here, I am going to discuss about the function CONCAT_WS.

This function concatenates a variable number of arguments with a delimiter specified in the first argument.

Syntax for the function

CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )

Demonstration

• Let’s create a simple table structure:

CREATE TABLE sel
       (
             Id INT,
             Customer VARCHAR(100),
             InsertDT datetime
       )
       GO

• Insert some random data to the table

INSERT INTO sel VALUES (00012, 'DELL',CURRENT_TIMESTAMP)
INSERT INTO sel VALUES (00012, 'INKP',CURRENT_TIMESTAMP)
INSERT INTO sel VALUES (00013, 'KOMT',CURRENT_TIMESTAMP)
INSERT INTO sel VALUES (00014, 'IHIS',CURRENT_TIMESTAMP)
INSERT INTO sel VALUES (00015, 'HIST',CURRENT_TIMESTAMP)
INSERT INTO sel VALUES (00013, 'KPMG',CURRENT_TIMESTAMP)
INSERT INTO sel VALUES (00012, 'ROSE',CURRENT_TIMESTAMP)

• Let’s apply the CONCAT_WS() function to concatenates a variable number of arguments with a delimiter.

SELECT
      CONCAT_WS (' - ', Id,Customer, InsertDT) ConcatenateAllColumns
FROM dbo.sel

Here is output of the above query – all three columns’ values of the table “sel” have been concatenated with “-” delimit.

ConcatenateAllColumns
------------------------------------
12 - DELL - Feb 22 2018  7:13AM
12 - INKP - Feb 22 2018  7:13AM
13 - KOMT - Feb 22 2018  7:13AM
14 - IHIS - Feb 22 2018  7:13AM
15 - HIST - Feb 22 2018  7:13AM
13 - KPMG - Feb 22 2018  7:13AM
12 - ROSE - Feb 22 2018  7:13AM

(7 row(s) affected)

For details, you can follow the MSDN link. Happy Learning!