MySQL基础教程10——函数—字符串函数
concat(拼接函数)
select concat(s1,s2,s3);
mysql> select concat('hello','mysql');
+-------------------------+
| concat('hello','mysql') |
+-------------------------+
| hellomysql |
+-------------------------+
1 row in set (0.01 sec)
lower(字符转换小写)
select lower(str);
mysql> select lower('HELLO');
+----------------+
| lower('HELLO') |
+----------------+
| hello |
+----------------+
1 row in set (0.01 sec)
upper(字符转换大写)
select upper(str);
mysql> select upper('hello');
+----------------+
| upper('hello') |
+----------------+
| HELLO |
+----------------+
1 row in set (0.01 sec)
lpad(左补充)
select lpad(str,n(填充后长度),pad);
mysql> select lpad('1',5,'0');
+-----------------+
| lpad('1',5,'0') |
+-----------------+
| 00001 |
+-----------------+
1 row in set (0.01 sec)
rpad(右补充)
select rpad(str,n(填充后长度),pad);
mysql> select rpad('1',5,'0');
+-----------------+
| rpad('1',5,'0') |
+-----------------+
| 10000 |
+-----------------+
1 row in set (0.01 sec)
trim(去除头尾空格)
select trim(str);
mysql> select trim(' hello mysql ');
+--------------------------------+
| trim(' hello mysql ') |
+--------------------------------+
| hello mysql |
+--------------------------------+
1 row in set (0.01 sec)
注意： 只会去除头尾的空格，内容中间的空格是不会受影响的。
substring(字符串截取)
select substring(str,start,len);
这里的起始索引值(start)为1
mysql> select substring('hello mysql',1,4);
+------------------------------+
| substring('hello mysql',1,4) |
+------------------------------+
| hell |
+------------------------------+
1 row in set (0.01 sec)
mysql> select substring('hello mysql',4,5);
+------------------------------+
| substring('hello mysql',4,5) |
+------------------------------+
| lo my |
+------------------------------+
1 row in set (0.01 sec)
案例
将员工工号补全六位数。
使用update和lpad将工号不足六位数的补足六位。
mysql> select * from users;
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 李明 |
+----+------+
4 rows in set
mysql> update users set id = lpad(id,6,'0');
Query OK, 4 rows affected
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from users;
+--------+------+
| id | name |
+--------+------+
| 000001 | 张三 |
| 000002 | 李四 |
| 000003 | 王五 |
| 000004 | 李明 |
+--------+------+
4 rows in set
