首页 > 代码库 > 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之字符函数