How to concatenate strings in MySQL and MariaDB

Tower Bridge, London
Tower Bridge concatenates two parts of London
and it looks like the || operator

The standard way to concatenate strings in MySQL is:

mysql> SET @var1 := 'Planet ';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @var2 := 'Earth';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CONCAT(@var1, @var2);
+----------------------+
| CONCAT(@var1, @var2) |
+----------------------+
| Planet Earth         |
+----------------------+

All examples in this article work without changes in MariaDB.

Dealing with NULL

But what if one of the variables is NULL? This is the case, for example, if a variable was never set. Well, in that case CONCAT() will return NULL.

Normally this is not the desired behaviour. Usually we want NULL to be considered as an empty string, instead. We can use CONCAT_WS(). This function adds a separator between other arguments, which is good for example to add a comma to a list or spaces to a human-readable message. If we don’t need it, we can just use an empty string as the first argument.

mysql> SELECT CONCAT(first_name, last_name) FROM employee;
+-------------------------------+
| CONCAT(first_name, last_name) |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS('', first_name, last_name) FROM employee;
+--------------------------------------+
| CONCAT_WS('', first_name, last_name) |
+--------------------------------------+
| Doe                                  |
+--------------------------------------+
1 row in set (0.00 sec)

We can consider >CONCAT_WS() as a NULL-safe concatenator.

But we can also replace NULLs with some default values using IFNULL():

mysql> SELECT CONCAT_WS(' ', IFNULL(first_name, ''), last_name)
    -> FROM employee;
+------------------------------------------------------------+
| CONCAT_WS(' ', IFNULL(first_name, ''), last_name) |
+------------------------------------------------------------+
|  Doe                                              |
+------------------------------------------------------------+

Oracle compatibility

EDIT: As Lonely DBA suggests in a comment, I was wrong in writing that this feature is for Oracle compatibility. It is actually ANSI SQL compatibility. Thanks for reporting my error.

For Oracle compatibility, MySQL also allows to use || as a concatenation operator, after setting sql_mode to PIPES_AS_CONCAT:

mysql> SET sql_mode := 'PIPES_AS_CONCAT';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT 'A' || 'B', 'A' || NULL;
+------------+-------------+
| 'A' || 'B' | 'A' || NULL |
+------------+-------------+
| AB         | NULL        |
+------------+-------------+

Note that this behaviour is different from Oracle’s. In fact, for Oracle sometimes NULL means NULL, but in some contexts it is treated as an empty string. The concatenation operator only returns NULL if both operands are NULL. More info in Oracle documentation: Nulls and Concatenation Operator.

It also is worth noting that, if we do this, we cannot use || as an OR operator, and its operands are always considered as strings:

mysql> SELECT 1 || 0;
+--------+
| 1 || 0 |
+--------+
| 10     |
+--------+

If you want to use this operator in a stored procedure or a trigger, just be sure to set the proper sql_mode:

SET sql_mode := 'PIPES_AS_CONCAT';
CREATE PROCEDURE procedure_name() ...

In fact, stored procedures and triggers will run with the same sql_mode that was set during its creation.

Toodle pip,
Federico

Photo credit: (C) Graham Hogg and licensed for reuse under this Creative Commons Licence

Comments (2)

Leave a Reply

Your email address will not be published. Required fields are marked *