MySQL tutorial: mysql concat() function

The MySQL CONCAT function accepts one or more string parameters and concatenates them into a string.

Description

• There is at least one parameter, which can be null.
• If all parameters are non-binary strings, non-binary strings are returned.
• If the parameter contains any binary string, a binary string is returned.
• If the parameter is a number, it is converted to an equivalent non-binary string form.
• If any parameter is NULL, NULL is returned.

Syntax

CONCAT(str1,str2,...)

Examples

In the following example, concat is used to concatenate multiple strings.

mysql> select concat("hello", " world", "!");
+--------------------------------+
| concat("hello", " world", "!") |
+--------------------------------+
| hello world!                   |
+--------------------------------+
1 row in set (0.00 sec)

In the following example, a parameter use the Concat function.

mysql> select concat('hello');
+-----------------+
| concat('hello') |
+-----------------+
| hello           |
+-----------------+
1 row in set (0.00 sec)

In the following example, if any of the arguments are NULL, concat returns NULL.

mysql> select concat("hello", "world", NULL);
+--------------------------------+
| concat("hello", "world", NULL) |
+--------------------------------+
| NULL                           |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select concat("hello", NULL, "!");
+----------------------------+
| concat("hello", NULL, "!") |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.00 sec)

mysql> select concat(NULL, "world", "!");
+----------------------------+
| concat(NULL, "world", "!") |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.00 sec)

In the following example, the Concat function is used on a table column.

test table t :

mysql> select * from t;
+----+----------+--------+------+
| id | name     | status | age  |
+----+----------+--------+------+
|  1 | ylspirit |      1 |   22 |
|  2 | carol    |      1 |   21 |
|  3 | apple    |      1 |   19 |
|  4 | orange   |      2 |   22 |
|  5 | ben      |      2 |   23 |
|  6 | john     |      3 |   19 |
|  7 | jack     |      2 |   18 |
|  8 | han      |      1 |   17 |
|  9 | alin     |      4 |   25 |
| 10 | tom      |      1 |   18 |
+----+----------+--------+------+
10 rows in set (0.01 sec)

Results output:

mysql> select concat(name, " : ", age) from t;
+--------------------------+
| concat(name, " : ", age) |
+--------------------------+
| ylspirit : 22            |
| carol : 21               |
| apple : 19               |
| orange : 22              |
| ben : 23                 |
| john : 19                |
| jack : 18                |
| han : 17                 |
| alin : 25                |
| tom : 18                 |
+--------------------------+
10 rows in set (0.00 sec)

In the following example, the Concat function is used on the WHERE condition.

mysql> select concat(name, " : ", age) from t where name=concat('t','o','m');
+--------------------------+
| concat(name, " : ", age) |
+--------------------------+
| tom : 18                 |
+--------------------------+
1 row in set (0.00 sec)

Reference

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat