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'