mysql字符串函数

常用函数

CHARSET(s)	返回字符串s的字符集
CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合并为一个字符串
UCASE(s)	将字符串转换为大写
LCASE(s)	将字符串 s 的所有字母变成小写字母
LENGTH(s)	返回字符串s的长度（按照字节）
REPLACE(s,s1,s2)	用字符串 s2 替代字符串 s 中的字符串 s1
SUBSTRING(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串
TRIM(s)	去掉字符串 s 开始和结尾处的空格

快速入门案例

mysql> select * from student;
+----+--------+---------+---------+-------+
| id | name   | chinese | english | math  |
+----+--------+---------+---------+-------+
|  3 | 鲁智深 |   33.00 |   32.00 | 12.00 |
|  2 | 林冲   |   95.00 |   98.00 | 91.00 |
|  1 | 李逵   |   18.00 |   25.00 | 11.00 |
+----+--------+---------+---------+-------+
3 rows in set (0.04 sec)

//CHARSET(s)	返回字符串s的字符集
mysql> select charset(name) from student;
+---------------+
| charset(name) |
+---------------+
| utf8          |
| utf8          |
| utf8          |
+---------------+
3 rows in set (0.00 sec)

//CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合并为一个字符串
mysql> select concat(name,'语文',chinese) from student;
+-----------------------------+
| concat(name,'语文',chinese) |
+-----------------------------+
| 鲁智深语文33.00             |
| 林冲语文95.00               |
| 李逵语文18.00               |
+-----------------------------+
3 rows in set (0.00 sec)

//UCASE(s)  将字符串转换为大写
//LCASE(s)	将字符串 s 的所有字母变成小写字母
mysql> select ucase('abc'),lcase('DEF');
+--------------+--------------+
| ucase('abc') | lcase('DEF') |
+--------------+--------------+
| ABC          | def          |
+--------------+--------------+
1 row in set (0.00 sec)

//LENGTH(s)	返回字符串s的长度（按照字节）
mysql> select name,length(name),chinese,length(chinese) from student;
+--------+--------------+---------+-----------------+
| name   | length(name) | chinese | length(chinese) |
+--------+--------------+---------+-----------------+
| 鲁智深 |            9 |   33.00 |               5 |
| 林冲   |            6 |   95.00 |               5 |
| 李逵   |            6 |   18.00 |               5 |
+--------+--------------+---------+-----------------+
3 rows in set (0.00 sec)

//REPLACE(s,s1,s2)	用字符串 s2 替代字符串 s 中的字符串 s1
mysql> select replace(name,'智深','*') from student;
+--------------------------+
| replace(name,'智深','*') |
+--------------------------+
| 鲁*                      |
| 林冲                     |
| 李逵                     |
+--------------------------+
3 rows in set (0.00 sec)

//SUBSTRING(s, start, length)   从字符串 s 的 start 位置截取长度为 length 的子字符串
//注意substring下标从1开始
mysql> select chinese,substring(chinese,1,2) from student;
+---------+------------------------+
| chinese | substring(chinese,1,2) |
+---------+------------------------+
|   33.00 | 33                     |
|   95.00 | 95                     |
|   18.00 | 18                     |
+---------+------------------------+
3 rows in set (0.00 sec)

综合练习

将ename的首字母显示为大写（数据库中数据并没改变）

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | smith  | clerk     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | martin | salesman  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | clark  | manager   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | scott  | analyst   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | king   | president | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | iurner | salesman  | 7698 | 1981-09-08 | 1500.00 |    NULL |     30 |
|  7900 | james  | clerk     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | ford   | analyst   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | miller | clerk     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

mysql> select ename,concat(ucase(substring(ename,1,1)),substring(ename,2,length(ename)-1)) from emp;
+--------+------------------------------------------------------------------------+
| ename  | concat(ucase(substring(ename,1,1)),substring(ename,2,length(ename)-1)) |
+--------+------------------------------------------------------------------------+
| smith  | Smith                                                                  |
| allen  | Allen                                                                  |
| ward   | Ward                                                                   |
| jones  | Jones                                                                  |
| martin | Martin                                                                 |
| blake  | Blake                                                                  |
| clark  | Clark                                                                  |
| scott  | Scott                                                                  |
| king   | King                                                                   |
| iurner | Iurner                                                                 |
| james  | James                                                                  |
| ford   | Ford                                                                   |
| miller | Miller                                                                 |
+--------+------------------------------------------------------------------------+
13 rows in set (0.03 sec)