SQL Server CONCAT_WS() Function

 The CONCAT_WS() function in SQL Server is used to concatenate strings with a specified separator. This function was introduced in SQL Server 2017.

Syntax

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

• separator: The string that will be used as the separator between the concatenated values.
• string1, string2, ..., stringN: The strings or expressions to concatenate.

Key Points

1. Null Handling: If any value in the list is NULL, it is ignored (unlike CONCAT(), which includes NULL values as the string "NULL").
2. Separator: The separator is added between non-null values only.
3. Variable Arguments: You can pass a variable number of string arguments.

Example Usage

Basic Example

SELECT CONCAT_WS('-', '2024', '12', '26') AS Result;

Output:

Result
2024-12-26

Ignoring NULL Values

SELECT CONCAT_WS(',', 'John', NULL, 'Doe') AS Result;

Output:

Result
John,Doe

Combining Columns with a Separator

CREATE TABLE Employees (
    FirstName NVARCHAR(50),
    MiddleName NVARCHAR(50),
    LastName NVARCHAR(50)
);

INSERT INTO Employees (FirstName, MiddleName, LastName)
VALUES ('Alice', NULL, 'Smith'),
       ('Bob', 'J.', 'Brown');

SELECT CONCAT_WS(' ', FirstName, MiddleName, LastName) AS FullName
FROM Employees;

Output:

FullName
Alice Smith
Bob J. Brown

Use Cases

• Generating delimited strings like CSV or pipe-delimited formats.
• Concatenating names, addresses, or other string fields in a user-friendly format.
• Simplifying SQL code by replacing repetitive concatenation logic.

Let me know if you need further examples or assistance!