正文
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)
12.ASCII(str)
用法：返回字符的ascii码，如果传的值是字符串，则返回第一个字符的ASCII
举例：
mysql> select ASCII('MYSQL')
+----------------+
| ASCII('MYSQL') |
+----------------+
| 77 |
+----------------+
1 row in set (0.02 sec)mysql> select ASCII('M');
+------------+
| ASCII('M') |
+------------+
| 77 |
+------------+
1 row in set (0.00 sec)mysql> select ASCII('');
+-----------+
| ASCII('') |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
