There are a couple of approaches we can use to concatenate strings and numbers in MySQL.

• We can use the CONCAT() function, which concatenates its arguments.
• We can 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 nonbinary string form.

Example:

SELECT CONCAT('Player', 456);

Result:

Player456

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

SELECT 
    CONCAT('Player ', 456) AS "Option 1",
    CONCAT('Player', ' ', 456) AS "Option 2";

Result:

+------------+------------+
| Option 1   | Option 2   |
+------------+------------+
| Player 456 | Player 456 |
+------------+------------+

The Pipe Concatenation Operator (||)

This example uses the concatenation operator (||):

SELECT 'Player ' || 456;

Result:

Player 456

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, || is a synonym for the OR logical operator (although this is deprecated). Trying to use it as a concatenation operator may produce unexpected results if you don’t enable it as a pipe concatenation operator first.