The MySQL CONCAT_WS function is a string function that stands for Concatenate With Separator. It is used to concatenate multiple strings into a single string, with a specified separator between each pair of strings. The primary purpose of CONCAT_WS is to simplify the process of concatenating strings while automatically adding a separator between them.

Syntax

The basic syntax of the CONCAT_WS function is as follows:

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

separator: This is the separator that will be placed between each pair of strings. It can be any valid string or character.

string1, string2, …: These are the strings that you want to concatenate.

Example

Here’s a simple example to illustrate how the CONCAT_WS function works:

SELECT CONCAT_WS(', ', 'John', 'Doe', '123 Main Street', 'City') AS full_address;

In this example, the CONCAT_WS function concatenates the strings ‘John’, ‘Doe’, ‘123 Main Street’, and ‘City’ with a comma and a space (‘, ‘) as the separator. The result will be a single string representing a full address: ‘John, Doe, 123 Main Street, City’.

One advantage of CONCAT_WS over the regular CONCAT function is that it automatically handles the inclusion of the separator, even if some of the input strings are NULL. If any of the input strings are NULL, CONCAT_WS excludes the separator before the NULL value.

SELECT CONCAT_WS(', ', 'John', NULL, 'Doe', '123 Main Street', 'City') AS full_address;

In this case, the result will be ‘John, Doe, 123 Main Street, City’ without any extra separators around the NULL value.

In summary, the CONCAT_WS function in MySQL is a convenient way to concatenate strings with a specified separator, and it simplifies the process by automatically handling NULL values without introducing extra separators.