The CONCAT_WS function is used to concatenate multiple strings into a single string, using a specified separator between each string. The "WS" stands for "with separator." This function can be particularly useful in SQL databases like MySQL.
Syntax
The syntax for CONCAT_WS is as follows:
CONCAT_WS(separator, string1, string2, ..., stringN)
- separator: This is the string that will be used to separate the concatenated strings.
- string1, string2, ..., stringN: These are the strings that you want to concatenate. You can include as many strings as you need.
Example
Here’s a basic example to illustrate how to use CONCAT_WS:
SELECT CONCAT_WS(', ', 'Apple', 'Banana', 'Cherry') AS FruitList;
In this example, CONCAT_WS will concatenate the strings "Apple", "Banana", and "Cherry" with a comma and a space as the separator. The output will be:
FruitList
-------------------
Apple, Banana, Cherry
Important Notes
- If any of the strings being concatenated are NULL, CONCAT_WS will ignore those NULL values and will not include an extra separator for them.
- The separator itself should not be NULL; if the separator is NULL, CONCAT_WS will return NULL.
Real-World Usage
CONCAT_WS can be very useful when you are dealing with data that needs to be formatted neatly for display or when generating text-based reports. For instance, it can be used to join the components of an address, like so:
SELECT CONCAT_WS(' ', street, city, state, zip) AS FullAddress
FROM addresses;
In this query, if any of the fields are NULL, those empty fields won't result in extra spaces or separators, making it cleaner for output.