SQL Server's CONCAT_WS function is used to concatenate two or more strings into a single string, using a specified separator between the strings. The "WS" in CONCAT_WS stands for "with separator".

Syntax

The syntax for the CONCAT_WS function is as follows:

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

Where:
separator is the character or string used to separate the strings
string1, string2, string3, ... are the strings to be concatenated

Example

Here's an example of how the CONCAT_WS function works in practice:

SELECT CONCAT_WS('-', 'John', 'Doe', '123 Main St', 'Anytown', 'USA');

This would output the following result: John-Doe-123 Main St-Anytown-USA

In this example, the separator is a hyphen (-), and the five strings "John", "Doe", "123 Main St", "Anytown", and "USA" are concatenated into a single string with the separator in between.

One of the benefits of using CONCAT_WS over the standard CONCAT function is that it allows for easier handling of NULL values. With CONCAT_WS, if any of the input strings are NULL, they will be excluded from the resulting string, whereas with CONCAT, if any of the input strings are NULL, the resulting string will also be NULL.

Overall, the CONCAT_WS function can be a useful tool for concatenating strings with a specified separator in SQL Server.