Here are two ways to concatenate strings and numbers in MariaDB:

• Use the CONCAT() function, which concatenates its arguments.
• Use the pipe concatenation operator (||), which concatenates its operands.

Below are examples of each.

The CONCAT() Function

The CONCAT() function concatenates its arguments. Although this is a string function, it can handle numeric (and binary string) arguments. Any numeric value is converted to its equivalent binary string form (this is in contrast to MySQL, which returns a nonbinary string).

Example:

SELECT CONCAT('Agent', 47);

Result:

Agent47

Spaces can be added, either by adding the space to the existing string, or by concatenating including a third argument that consists solely of a space:

SELECT 
    CONCAT('Agent ', 47) AS "Option 1",
    CONCAT('Agent', ' ', 47) AS "Option 2";

Result:

+----------+----------+
| Option 1 | Option 2 |
+----------+----------+
| Agent 47 | Agent 47 |
+----------+----------+

If you don’t want the numeric value to be converted to its equivalent binary string form, you can explicitly cast it before the concatenation operation.

Example:

SELECT CONCAT('Agent', CAST(47 AS char));

Result:

Agent47

The Pipe Concatenation Operator (||)

This example uses the concatenation operator (||):

SELECT 'Agent ' || 47;

Result:

Agent 47

In this case, I appended a space to the first argument.

Note that the pipe concatenation operator first needs to be enabled before you can use it in this manner. By default, MariaDB treats || as a synonym for the OR logical operator. Trying to use it as a concatenation operator may produce unexpected results if you don’t enable it as a pipe concatenation operator first.