mysql 字符串相关函数

mysql 字符串相关函数

concat 字符串拼接函数
mysql> select concat('imooc','lizx');
+------------------------+
| concat('imooc','lizx') |
+------------------------+
| imooclizx |
+------------------------+
1 row in set (0.00 sec)

concat_ws 使用指定的连接符进行字符串拼接
mysql> select concat_ws('|','李志祥','前端工程师');
+--------------------------------------+
| concat_ws('|','李志祥','前端工程师') |
+--------------------------------------+
| 李志祥|前端工程师 |
+--------------------------------------+
1 row in set (0.00 sec)
format(数字,保留位数)) 数字格式化 ，遵循四舍五入
mysql> select format(12323.476,2);
+---------------------+
| format(12323.476,2) |
+---------------------+
| 12,323.48 |
+---------------------+
1 row in set (0.00 sec)
lower 将字符串转成小写
mysql> select lower("ENGINEER");
+-------------------+
| lower("ENGINEER") |
+-------------------+
| engineer |
+-------------------+
1 row in set (0.04 sec)
upper 和lower相反，将小写转换成大写
left 获取从左侧指定长度的字符串
mysql> select left('lizhixiang',5);
+----------------------+
| left('lizhixiang',5) |
+----------------------+
| lizhi |
+----------------------+
1 row in set (0.00 sec)

right 和left相反 获取从右侧指定长度的字符串
函数嵌套

demo

mysql> select upper(left("lizhixiang",7));
+-----------------------------+
| upper(left("lizhixiang",7)) |
+-----------------------------+
| LIZHIXI |
+-----------------------------+
1 row in set (0.00 sec)
length 获取字符串长度
mysql> select length('lizhixiang');
+----------------------+
| length('lizhixiang') |
+----------------------+
| 10 |
+----------------------+
1 row in set (0.00 sec)
ltrim 删除字符串左侧空格
mysql> select ltrim(' lizx');
+------------------+
| ltrim(' lizx') |
+------------------+
| lizx |
+------------------+
1 row in set (0.00 sec)

mysql> select length(' lizx');
+-------------------+
| length(' lizx') |
+-------------------+
| 7 |
+-------------------+
1 row in set (0.00 sec)

mysql> select length( ltrim(' lizx'));
+---------------------------+
| length( ltrim(' lizx')) |
+---------------------------+
| 4 |
+---------------------------+
1 row in set (0.00 sec)
同样 rtrim 和ltrim相反 去除字符串右侧空格
trim 去掉字符串的空格
mysql> select trim(both '' from '**lizhixiang');
+----------------------------------------+
| trim(both '' from '**lizhixiang**') |
+----------------------------------------+
| lizhixiang |
+----------------------------------------+
1 row in set (0.00 sec)
substring(字符串，起始位置，截取长度)字符串截取
mysql> select substring('lizhixiang',1,6);
+-----------------------------+
| substring('lizhixiang',1,6) |
+-----------------------------+
| lizhix |
+-----------------------------+
1 row in set (0.00 sec)
**注意：和编程语言不同，mysql的起始位置是从1开始，而非0，起始位置可以是负值，表示倒序开始截取

like 查找包含指定内容的字符串
mysql> select * from price where brand_name like '%联%';
+----------+-------+------------------+------------+------+
| goods_id | price | goods_name | brand_name | sale |
+----------+-------+------------------+------------+------+
| 1 | 500 | 联想笔记本双肩包 | 联想 | 0 |
| 2 | 12500 | THINKPAD X220T | 联想 | 0.2 |
+----------+-------+------------------+------------+------+
2 rows in set (0.00 sec)
此外，还有replace方法，用于替换字符串中的指定部分

mysql数字运算符函数

ceil 进一取整
mysql> select ceil(8/3);
+-----------+
| ceil(8/3) |
+-----------+
| 3 |
+-----------+
1 row in set (0.05 sec)
TRUNCATE 数字截取
mysql> select truncate(123.345,2);
+---------------------+
| truncate(123.345,2) |
+---------------------+
| 123.34 |
+---------------------+
1 row in set (0.00 sec)
取余函数用法基本一致

DIV 整数除法
FLOOR 舍一取整
MOD 取余
POWER 幂运算
ROUND 四舍五入