The SQL CONCAT_WS function is used to concatenate values from multiple columns or expressions with a specified separator. The name CONCAT_WS stands for “Concatenate With Separator.” This function is particularly useful when you want to combine the values of multiple columns or expressions into a single string, and you want to include a separator between them.
Syntax
The basic syntax of the CONCAT_WS function is as follows:
CONCAT_WS(separator, expression1, expression2, ..., expressionN)
separator: This is the string that will be used to separate the values. It can be any character or string.
expression1, expression2, …, expressionN: These are the expressions or columns whose values you want to concatenate.
Example
Here’s an example to illustrate how to use the CONCAT_WS function:
SELECT
CONCAT_WS(', ', first_name, last_name) AS full_name
FROM employees;
In this example, assuming there is a table named “employees” with columns first_name and last_name, the CONCAT_WS function is used to concatenate the values of these two columns with a comma and a space as the separator. The result will be a new column named “full_name” that contains the combined full names of the employees.
It’s important to note that the CONCAT_WS function automatically handles the separator for you, placing it only between the non-null values. If any of the values being concatenated is null, the separator is not added.
Here’s another example that demonstrates the handling of null values:
SELECT
CONCAT_WS(' - ', product_name, product_description) AS product_info
FROM products;
In this case, the CONCAT_WS function is used to concatenate the values of the “product_name” and “product_description” columns with a hyphen and a space as the separator. If the “product_description” is null for a particular row, the function will still produce a meaningful result without an extra separator.
In summary, the CONCAT_WS function is a convenient tool in SQL for concatenating values with a specified separator, making it easy to create composite strings from multiple columns or expressions in a database query.
