字符串函数

concat

字符连接

mysql> select concat(id,"-",name,"-",age,"-",sex) as user from user;
+-----------------+
| user            |
+-----------------+
| 1-小明-19-男    |
| 2-小红-11-女    |
| 3-小环-19-女    |
| 4-小白-21-男    |
+-----------------+
mysql> SELECT concat("id",'-',"name") as path;
+---------+
| path    |
+---------+
| id-name |
+---------+

CONCAT_WS

使用指定的分隔符连接字符串.

mysql> SELECT CONCAT_WS('*',"id","name","age");
+----------------------------------+
| CONCAT_WS('*',"id","name","age") |
+----------------------------------+
| id*name*age                      |
+----------------------------------+

FORMAT

数字格式化。FORMAT(数字，保留位数)

mysql> SELECT FORMAT(123.456,2);
+-------------------+
| FORMAT(123.456,2) |
+-------------------+
| 123.46            |
+-------------------+

LOWER()

转换成小写字母

mysql> SELECT LOWER("MYSQL");
+----------------+
| LOWER("MYSQL") |
+----------------+
| mysql          |
+----------------+

UPPER()

转换成大写

mysql> SELECT UPPER("mysql");
+----------------+
| UPPER("mysql") |
+----------------+
| MYSQL          |
+----------------+

LEFT()

获取左侧字符，LEFT(字符串，获取位数);

mysql> SELECT LEFT('mysql',2);
+-----------------+
| LEFT('mysql',2) |
+-----------------+
| my              |
+-----------------+

RIGHT()

获取右侧字符

mysql> SELECT RIGHT('mysql',1);
+------------------+
| RIGHT('mysql',1) |
+------------------+
| l                |
+------------------+

嵌套

mysql> SELECT LOWER(LEFT('MYSQL',3));
+------------------------+
| LOWER(LEFT('MYSQL',3)) |
+------------------------+
| mys                    |
+------------------------+

LENGTH()

获取字符串长度，空格也会被获取到；

mysql> SELECT LENGTH('mysql');
+-----------------+
| LENGTH('mysql') |
+-----------------+
|               5 |
+-----------------+

LTRIM()

删除左侧空格

mysql> SELECT LENGTH(LTRIM(' MYSQL'));
+-------------------------+
| LENGTH(LTRIM(' MYSQL')) |
+-------------------------+
|                       5 |
+-------------------------+

RTRIM()

删除右侧空格

mysql> SELECT LENGTH(RTRIM('MYSQL '));
+-------------------------+
| LENGTH(RTRIM('MYSQL ')) |
+-------------------------+
|                       5 |
+-------------------------+

TRIM()

删除左右空格

mysql> SELECT LENGTH(TRIM(' MYSQL '));
+-------------------------+
| LENGTH(TRIM(' MYSQL ')) |
+-------------------------+
|                       5 |
+-------------------------+

REPLACE()

字符串替换

mysql> SELECT REPLACE('mysql',"my","!");
+---------------------------+
| REPLACE('mysql',"my","!") |
+---------------------------+
| !sql                      |
+---------------------------+

SUBSTRING()

字符串截取

mysql> SELECT SUBSTRING('MYSQL',3);
+----------------------+
| SUBSTRING('MYSQL',3) |
+----------------------+
| SQL                  |
+----------------------+
mysql> SELECT SUBSTRING('MYSQL',-1);
+-----------------------+
| SUBSTRING('MYSQL',-1) |
+-----------------------+
| L                     |
+-----------------------+

LIKE()

模糊搜索，存在的字符；

• % 通配符（匹配所有内容）
• _ 匹配一个字符

mysql> SELECT * FROM USER WHERE name LIKE('%明%');
+----+--------+------+------+
| id | name   | age  | sex  |
+----+--------+------+------+
|  1 | 小明   |   19 | 男   |
+----+--------+------+------+

NOT LEKE()

模糊搜索，非存在的字符。

mysql> SELECT * FROM USER WHERE name NOT LIKE("%明%");
+----+--------+------+------+
| id | name   | age  | sex  |
+----+--------+------+------+
|  2 | 小红   |   11 | 女   |
|  3 | 小环   |   19 | 女   |
|  4 | 小白   |   21 | 男   |
+----+--------+------+------+