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".
The syntax for the
CONCAT_WS function is as follows:
CONCAT_WS(separator, string1, string2, string3, ...)
separator is the character or string used to separate the strings
string1, string2, string3, ... are the strings to be concatenated
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.
CONCAT_WS function can be a useful tool for concatenating strings with a specified separator in SQL Server.