Concat_WS function in SQL Server 2017
Introduction
In SQL Server 2017, a new string function Concat_WS introduced which concats two or more expression into a single string.
Explanation
Concat_WS function concatenates the two or more expressions into a single string and adds a separator between them.
Syntax
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.