MySQL基础教程10——函数—字符串函数
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
