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'