首页 > 代码库 > MySQL之字符函数
MySQL之字符函数
MySql中提供一些函数对我们的开发有很多的帮助,下面就把MysQL提供的一些常用函数整理下,首先是字符处理函数:
1.CONCAT()
用法:字符串链接函数,将字符串字段连结在一块
举例:
select concat(‘MYSQL‘,‘Functioin‘,‘Test‘,‘asian‘);
结果:
+-----------------------------------------------+| concat(‘MYSQL‘,‘Functioin‘,‘Test‘,‘asian‘) |+-----------------------------------------------+| MYSQLFunctioinTestLasian |+-----------------------------------------------+
2.CONCAT_WS()
用法举例:使用指定的分隔符将字符链接在一块
举例:
select CONCAT_WS(‘****‘,‘Mysql‘,‘Function‘,‘test‘);
结果:
+---------------------------------------------+| CONCAT_WS(‘****‘,‘Mysql‘,‘Function‘,‘test‘) |+---------------------------------------------+| Mysql****Function****test |
3.FORMAT()
用法举例:数字格式化
举例:
select FORMAT(3434.343434,3);
结果:
+-----------------------+| FORMAT(3434.343434,3) |+-----------------------+| 3,434.343 |+-----------------------+
4.LOWER()
用法:转换成小写字符
举例:
select LOWER(‘MYSQL‘);
结果:
+----------------+| LOWER(‘MYSQL‘) |+----------------+| mysql |+----------------+
5.UPPER()
用法:和LOWER()用法相反
6.LEFT()
用法:获取左侧字符
举例:
mysql> select LEFT(‘MYSQL‘,1);+-----------------+| LEFT(‘MYSQL‘,1) |+-----------------+| M |+-----------------+1 row in set (0.01 sec)mysql> select LEFT(‘MYSQL‘,5);+-----------------+| LEFT(‘MYSQL‘,5) |+-----------------+| MYSQL |+-----------------+1 row in set (0.00 sec)mysql> select LEFT(‘MYSQL‘,7);+-----------------+| LEFT(‘MYSQL‘,7) |+-----------------+| MYSQL |+-----------------+1 row in set (0.00 sec)
当操作字符长度限制的时候会返回真个字符串,但是必须要有数字参数否则会报错
6.RIGHT()
用法:用法和LEFT()类似
mysql> select RIGHT(‘MYSQL‘,7);+------------------+| RIGHT(‘MYSQL‘,7) |+------------------+| MYSQL |+------------------+1 row in set (0.00 sec)mysql> select RIGHT(‘MYSQL‘,5);+------------------+| RIGHT(‘MYSQL‘,5) |+------------------+| MYSQL |+------------------+1 row in set (0.00 sec)mysql> select RIGHT(‘MYSQL‘,2);+------------------+| RIGHT(‘MYSQL‘,2) |+------------------+| QL |+------------------+1 row in set (0.00 sec)
7.LENGTH()
用法:获取字符的长度(包括字符内部的空格长度)
mysql> select LENGTH(‘MYSQL‘);+-----------------+| LENGTH(‘MYSQL‘) |+-----------------+| 5 |+-----------------+1 row in set (0.00 sec)mysql> select LENGTH(‘MY SQL‘);+-------------------+| LENGTH(‘MY SQL‘) |+-------------------+| 7 |+-------------------+1 row in set (0.00 sec)mysql> select LENGTH(‘ MY SQL ‘);+---------------------+| LENGTH(‘ MY SQL ‘) |+---------------------+| 9 |+---------------------+1 row in set (0.00 sec)
8.TRIM()
用法:删除前导和后导的字符
mysql> select TRIM(‘ MYSQL ‘) -> ;+-----------------+| TRIM(‘ MYSQL ‘) |+-----------------+| MYSQL |+-----------------+1 row in set (0.00 sec)mysql> select LENGTH(TRIM(‘ MYSQL ‘));+-------------------------+| LENGTH(TRIM(‘ MYSQL ‘)) |+-------------------------+| 5 |+-------------------------+1 row in set (0.00 sec)mysql> select LENGTH(‘ MYSQL ‘);+-------------------+| LENGTH(‘ MYSQL ‘) |+-------------------+| 7 |+-------------------+1 row in set (0.00 sec)
9.LTRIM(),RTRIM()
这两个函数分别是删除前导空格和后导空格
10.SUBSTRING()
用法:截取字符的子串,第一个参数是操作的字符串,第二个是返回子串开始的位置,第三个是返回子串的数目,默认是返回全部,如果
超过长度则返回全部剩余的子串
举例:
mysql> select SUBSTRING(‘MYSQL‘,2);+----------------------+| SUBSTRING(‘MYSQL‘,2) |+----------------------+| YSQL |+----------------------+1 row in set (0.00 sec)mysql> select SUBSTRING(‘MYSQL‘,2,2);+------------------------+| SUBSTRING(‘MYSQL‘,2,2) |+------------------------+| YS |+------------------------+1 row in set (0.00 sec)mysql> select SUBSTRING(‘MYSQL‘,2,4);+------------------------+| SUBSTRING(‘MYSQL‘,2,4) |+------------------------+| YSQL |+------------------------+1 row in set (0.00 sec)
11.REPLACE()
用法:替换字符串函数,第一个参数是操作对象,第二个是待替换的字符串,第三个是替换后字符串
举例:
mysql> select REPLACE(‘mysql‘,‘my‘,‘replace‘);+---------------------------------+| REPLACE(‘mysql‘,‘my‘,‘replace‘) |+---------------------------------+| replacesql |+---------------------------------+1 row in set (0.00 sec)
MySQL之字符函数
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。