In SQL Server 2017, a new string function Concat_WS introduced which concats two or more expression into a single string.


Concat_WS function concatenates the two or more expressions into a single string and adds a separator between them.


CONCAT_WS (separator, argument 1, argument 2, ………….)

Separator can be of a character type.

Expression can be of any type.

Concat_WS requires minimum of two arguments and one separator otherwise it will throw an error.

Let’s see an example of it

Using below query I am trying to concatenating the values of different columns into a single string using a separator from table sys.tables.

SELECT CONCAT_WS ( ' – ' , object_id, type, type_desc, CONVERT (date, create_date)) as Result
FROM sys.tables

Here, you can see in the output that the values get concatenated with separator ‘ – ‘ .

Concat_WS also ignores the NULL values during concatenation and does not add the separator. To handle such scenarios, you can use the ISNULL function to handle the NULL values.

The following example ignores the NULL values passed in the arguments 

SELECT CONCAT_WS (‘  ‘, ‘Kapil’, NULL, ‘Singh’, NULL , ‘Kumawat’ )

Hope, you will like this post.

See Also: