CONCAT_WS

Returns a string that is the result of concatenating two or more strings with a delimiter. CONCAT_WS stands for CONCAT With Separator.

CONCAT_WS ( separator , string1 , string2 [ , ...stringN ] )

Parameters

Parameter Name	Description	Data Type
`separator`	The string to use as a delimiter between the input strings.	`varchar`, `nvarchar`, `char`, `nchar`
`string1`, `string2`, ..., `stringN`	The strings to concatenate. The maximum number of strings that can be concatenated is 254. The first parameter is the separator, and the subsequent parameters are the strings to be joined.	`varchar`, `nvarchar`, `char`, `nchar`

Returns the concatenated string. The data type of the return value is determined by the concatenation of the input string types.

CONCAT_WS ignores NULL values. It does not insert the separator between NULL values.
If the separator is NULL, CONCAT_WS returns NULL.
If all input strings are NULL, CONCAT_WS returns an empty string ('').
The result is implicitly converted to the highest precedence type in the argument list. For more information, see Type Precedence (Transact-SQL).
For information about the maximum number of arguments supported, see Maximum routine nesting levels.


SELECT CONCAT_WS(' ', 'Microsoft', 'SQL', 'Server');
-- Returns: Microsoft SQL Server


SELECT CONCAT_WS('-', 'Customer', NULL, '123', NULL, 'Order');
-- Returns: Customer-123-Order


SELECT CONCAT_WS(' | ', 'Apples', 'Bananas', 'Cherries');
-- Returns: Apples | Bananas | Cherries

For earlier versions of SQL Server, you may need to use the + operator or the STUFF function with FOR XML PATH for similar functionality.