In MariaDB, CONCAT() is a built-in string function that performs a string concatenation on its arguments. It returns the string that results from concatenating its arguments.

Concatenation is the operation of joining two or more strings end-to-end.

CONCAT() accepts one or more arguments, all of which are returned as a single concatenated string.

Syntax

The syntax goes like this:

CONCAT( str1, str2, ... )

Where str1, str2, … represent the string arguments for which to concatenate.

Example

Here’s a basic example:

SELECT CONCAT('Homer', 'Jay');

Result:

+------------------------+
| CONCAT('Homer', 'Jay') |
+------------------------+
| HomerJay               |
+------------------------+

In this case, we concatenated two strings.

Here it is again, but this time we add a space in between:

SELECT CONCAT('Homer', ' ', 'Jay');

Result:

+-----------------------------+
| CONCAT('Homer', ' ', 'Jay') |
+-----------------------------+
| Homer Jay                   |
+-----------------------------+

In this case, we concatenated three strings.

Here it is with five:

SELECT CONCAT('Homer', ' ', 'Jay', ' ', 'Simpson');

Result:

+---------------------------------------------+
| CONCAT('Homer', ' ', 'Jay', ' ', 'Simpson') |
+---------------------------------------------+
| Homer Jay Simpson                           |
+---------------------------------------------+

Binary Strings

If any of the arguments is a binary string, the result is a binary string:

SELECT CONCAT(BINARY 'Homer', 'Jay');

Result:

+-------------------------------+
| CONCAT(BINARY 'Homer', 'Jay') |
+-------------------------------+
| HomerJay                      |
+-------------------------------+

We can use the COLLATION() function to check the collation of the result:

SELECT COLLATION(CONCAT(BINARY 'Homer', 'Jay'));

Result:

+------------------------------------------+
| COLLATION(CONCAT(BINARY 'Homer', 'Jay')) |
+------------------------------------------+
| binary                                   |
+------------------------------------------+

If we remove the BINARY operator, we get a different result:

SELECT COLLATION(CONCAT('Homer', 'Jay'));

Result:

+-----------------------------------+
| COLLATION(CONCAT('Homer', 'Jay')) |
+-----------------------------------+
| utf8_general_ci                   |
+-----------------------------------+

Also, according to the MariaDB documentation, numeric arguments are converted to their equivalent binary string form. You can avoid this by doing an explicit type cast (using CAST() or CONVERT()).

Null Arguments

If any of the arguments are null, CONCAT() returns null.

However, there is one exception: When in Oracle mode (i.e. sql_mode=ORACLE), any null arguments are simply ignored.

First, let’s see how CONCAT() behaves in default mode.

Set our session to default mode:

SET SESSION sql_mode=DEFAULT;

Now run CONCAT() with a null argument:

SELECT CONCAT('Homer', ' ', null, ' ', 'Simpson');

Result:

+--------------------------------------------+
| CONCAT('Homer', ' ', null, ' ', 'Simpson') |
+--------------------------------------------+
| NULL                                       |
+--------------------------------------------+

As expected, the result is null.

Now let’s switch our session to Oracle mode:

SET SESSION sql_mode=ORACLE;

And now let’s run the previous CONCAT() example again:

SELECT CONCAT('Homer', ' ', null, ' ', 'Simpson');

Result:

+--------------------------------------------+
| CONCAT('Homer', ' ', null, ' ', 'Simpson') |
+--------------------------------------------+
| Homer  Simpson                             |
+--------------------------------------------+

This time it ignored the null argument, and concatenated all remaining non-null arguments. If you look closely, you’ll see that it concatenated both spaces, so there’s a double space in between Homer and Simpson.

For a null-safe alternative, use CONCAT_WS(). That function ignores null values, even when not in Oracle mode.

Alternatively, you can use IFNULL() to provide an alternative (string) value for null values (such as an empty string).

Single Argument

Calling CONCAT() with only one argument simply returns that argument:

SELECT CONCAT('Homer');

Result:

+-----------------+
| CONCAT('Homer') |
+-----------------+
| Homer           |
+-----------------+

Missing Argument

Calling CONCAT() without passing any arguments results in an error:

SELECT CONCAT();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'CONCAT'