How CONCAT_WS() Works in MariaDB

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'