In MariaDB, CONCAT_WS() is a built-in string function that stands for Concatenate With Separator.
CONCAT_WS() performs a string concatenation on its arguments, with the first argument being the separator for the remaining arguments.
Concatenation is the operation of joining two or more strings end-to-end.
CONCAT_WS() accepts two or more arguments (although providing just two arguments would result in nothing being concatenated, due to the first argument being the separator, and the second being a single string to be concatenated with… nothing else).
Syntax
The syntax goes like this:
CONCAT_WS(separator,str1,str2,...)Where separator is the string to be used as a separator, and str1, str2, … represent the string arguments for which to concatenate.
Example
Here’s a basic example:
SELECT CONCAT_WS( ', ', 'Milk', 'Cheese', 'Bread');Result:
+---------------------------------------------+ | CONCAT_WS( ', ', 'Milk', 'Cheese', 'Bread') | +---------------------------------------------+ | Milk, Cheese, Bread | +---------------------------------------------+
In this case, we concatenated three strings using a comma and a space as the separator.
Here’s another one that uses a different separator:
SELECT CONCAT_WS('-', 'Blue', 'Red', 'Green');Result:
+----------------------------------------+
| CONCAT_WS('-', 'Blue', 'Red', 'Green') |
+----------------------------------------+
| Blue-Red-Green |
+----------------------------------------+
CONCAT_WS() is similar to the CONCAT() function. One of the benefits of CONCAT_WS() over CONCAT() becomes apparent when concatenating many strings.
To do the previous example with CONCAT(), we’d need to repeat the separator in between each string.
Like this:
SELECT CONCAT('Blue', '-', 'Red', '-', 'Green');Result:
+------------------------------------------+
| CONCAT('Blue', '-', 'Red', '-', 'Green') |
+------------------------------------------+
| Blue-Red-Green |
+------------------------------------------+
This could get unwieldy if we had a lot of strings to concatenate.
No Separator
Providing an empty string as the separator concatenates the strings without a separator:
SELECT CONCAT_WS('', 'Blue', 'Red', 'Green');Result:
+---------------------------------------+
| CONCAT_WS('', 'Blue', 'Red', 'Green') |
+---------------------------------------+
| BlueRedGreen |
+---------------------------------------+
In this case we get the same result we’d get when using CONCAT() to concatenate those three strings.
It’s important to provide a separator, even if it’s empty. Failing to provide a separator will result in the first concatenation string being used as the separator, which is probably not what you want.
Example:
SELECT CONCAT_WS('Blue', 'Red', 'Green', 'Orange');Result:
+---------------------------------------------+
| CONCAT_WS('Blue', 'Red', 'Green', 'Orange') |
+---------------------------------------------+
| RedBlueGreenBlueOrange |
+---------------------------------------------+
In this case, Blue is the first argument, and so it ends up being used as the separator.
Concatenating Null Arguments
Another benefit that CONCAT_WS() has over CONCAT() is that it’s null-safe.
If any of the arguments to be concatenated are null, CONCAT_WS() ignores them. The CONCAT() function on the other hand, returns null (unless it’s in Oracle mode, in which case it ignores null arguments).
Let’s call CONCAT_WS() with a null argument:
SELECT CONCAT_WS('-', 'Blue', NULL, 'Green');Result:
+---------------------------------------+
| CONCAT_WS('-', 'Blue', NULL, 'Green') |
+---------------------------------------+
| Blue-Green |
+---------------------------------------+
As expected, CONCAT_WS() skipped over the null argument, and concatenated the remaining arguments.
This means that, if we provide an empty string as the separator, we can use CONCAT_WS() as a null-safe version of CONCAT():
SELECT CONCAT_WS('', 'Blue', NULL, 'Green');Result:
+--------------------------------------+
| CONCAT_WS('', 'Blue', NULL, 'Green') |
+--------------------------------------+
| BlueGreen |
+--------------------------------------+
Null Separator
Providing a null separator is a different story. Doing this returns null.
SELECT CONCAT_WS(NULL, 'Blue', 'Red', 'Green');Result:
+-----------------------------------------+ | CONCAT_WS(NULL, 'Blue', 'Red', 'Green') | +-----------------------------------------+ | NULL | +-----------------------------------------+
Binary Strings
If any of the arguments is a binary string, the result is a binary string:
SELECT CONCAT_WS(', ', BINARY 'Apple', 'Orange');Result:
+-------------------------------------------+
| CONCAT_WS(', ', BINARY 'Apple', 'Orange') |
+-------------------------------------------+
| Apple, Orange |
+-------------------------------------------+
We can use the COLLATION() function to check the collation of the result:
SELECT COLLATION(CONCAT_WS(', ', BINARY 'Apple', 'Orange'));Result:
+------------------------------------------------------+
| COLLATION(CONCAT_WS(', ', BINARY 'Apple', 'Orange')) |
+------------------------------------------------------+
| binary |
+------------------------------------------------------+
If we remove the BINARY operator, we get a different result:
SELECT COLLATION(CONCAT_WS(', ', 'Apple', 'Orange'));Result:
+-----------------------------------------------+
| COLLATION(CONCAT_WS(', ', 'Apple', 'Orange')) |
+-----------------------------------------------+
| utf8_general_ci |
+-----------------------------------------------+
Single Argument
Calling CONCAT_WS() with only one argument returns an error:
SELECT CONCAT_WS('Blue');Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'CONCAT_WS'
Missing Arguments
Calling CONCAT_WS() without passing any arguments results in an error:
SELECT CONCAT_WS();Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'CONCAT_WS'