SQL Server CONCAT_WS() Function

The SQL Server (Transact-SQL) CONCAT_WS() function is used to concatenate two or more expressions together and adds a separator between each of the concatenated expressions. This function may have one or more expressions. Note that, if any of the expressions is a NULL, this function ignores NULL values during concatenation, and does not add the separator between NULL values.

Syntax

CONCAT_WS(separator, expr1, expr2, ... expr_n)

Parameters

Return Value

Returns the concatenated string.

Example 1:

The example below shows the usage of CONCAT_WS() function.

SELECT CONCAT_WS(' ', 'SQL', 'Tutorial');
Result: 'SQL Tutorial'

SELECT CONCAT_WS(' ', 'Learning', 'SQL', 'is', 'fun!.');
Result: 'Learning SQL is fun!.'

SELECT CONCAT_WS(', ', 10, 20, 30, 40);
Result: '10, 20, 30, 40'

SELECT CONCAT_WS(' = ', 'Sum', 25 + 25);
Result: 'Sum = 50'

SELECT CONCAT_WS(', ', 10, 20, NULL, 40);
Result: '10, 20, 40'

Example 2:

Consider a database table called Employee with the following records:

In the query below, the CONCAT_WS() function is used to concatenate records of column FirstName and column LastName.

SELECT *, CONCAT_WS(' ', FirstName, LastName) AS FullName FROM Employee;

This will produce the result as shown below: