一、MySQL中的Concat()函数
1. 含义:
将多个字符串连接成一个字符串。
2. 语法:
concat(str1, str2,…)
返回结果为连接参数产生的字符串,如果有任何一个参数为null,则返回值为null。
3. 演示:
mysql> select * from Customer;
+-------+-------+
| CustomerCode | name |
+-------+-------+
| 150920111 | 王安石 |
| 160120320 | 徐某某 |
| 160400634 | 刘某某 |
| 160730322 | 陈某某 |
| 160800230 | 胡某某 |
| 161010526 | 杨某某 |
| 161140218 | 章某某 |
+-------+-------+;
mysql> select Concat(CustomerCode,name) as 学号+姓名 from Customer;
+-------+-------+
| 学号+姓名 |
+-------+-------+
| 150920111王安石 |
| 160120320徐某某 |
| 160400634刘某某 |
| 160730322陈某某 |
| 160800230胡某某 |
| 161010526杨某某 |
| 161140218章某某 |
+-------+-------+;
二、利用Concat()函数批量添加前缀/后缀
例1:要求将前7个CustomerCode前缀批量添加字符串“学号:”
mysql>
UPDATE Customer
SET CustomerCode = Concat('学号:', CustomerCode)
WHERE
CustomerCode BETWEEN 150920111 AND 161010526
+-------+-------+
| CustomerCode | name |
+-------+-------+
| 学号:150920111 | 王安石 |
| 学号:160120320 | 徐某某 |
| 学号:160400634 | 刘某某 |
| 学号:160730322 | 陈某某 |
| 学号:160800230 | 胡某某 |
| 学号:161010526 | 杨某某 |
| 161140218 | 章某某 |
+-------+-------+;
例2:假设要求将CustomerCode后缀批量添加数字000
mysql>
UPDATE Customer SET CustomerCode = Concat(CustomerCode, 000)
+-------+-------+
| CustomerCode | name |
+-------+-------+
| 150920111000 | 王安石 |
| 160120320000 | 徐某某 |
| 160400634000 | 刘某某 |
| 160730322000 | 陈某某 |
| 160800230000 | 胡某某 |
| 161010526000 | 杨某某 |
| 161140218000 | 章某某 |
+-------+-------+;
参考链接:
[1]:https://blog.csdn.net/u011805135/article/details/91567418