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