SQL String Function CONCAT_WS()

In SQL, the CONCAT_WS() function is used to concatenate two or more strings into a single string, with a specified separator between them. The WS in the function name stands for "with separator".

Here is an example of using the CONCAT_WS() function to concatenate the first name, middle name, and last name of employees in a employees table, separated by a space:

SELECT CONCAT_WS(' ', first_name, middle_name, last_name) AS full_name FROM employees;

In this example, the CONCAT_WS() function is used to concatenate the first_name, middle_name, and last_name columns of the employees table, separated by a space. The first argument of the function specifies the separator to be used between the strings.

The CONCAT_WS() function can also handle NULL values. If any of the input strings are NULL, the separator is not added between them. For example, if an employee does not have a middle name, the separator will not be added between the first name and last name.

It's important to note that the order of the input strings in the CONCAT_WS() function matters. The first argument specifies the separator, and the subsequent arguments specify the strings to be concatenated, in the order in which they should be concatenated.

Also, like the CONCAT() function, the CONCAT_WS() function returns NULL if all input strings are NULL. To handle this, we can use the COALESCE() function to replace NULL values with a default value or an empty string, before using the CONCAT_WS() function. For example:

SELECT CONCAT_WS(' ', COALESCE(first_name, ''), COALESCE(middle_name, ''), COALESCE(last_name, '')) AS full_name FROM employees;

In this example, the COALESCE() function is used to replace any NULL values in the first_name, middle_name, and last_name columns with an empty string, before using the CONCAT_WS() function to concatenate the strings.