MySQL字符函数

本文以实例形式来讲解MySQL的函数

按照功能的分类

1 字符函数

2 数值运算符与函数

3 比较运算符与函数

4 日期时间函数

5 信息函数

6 聚合函数

7 加密函数

字符函数分类

CONCAT()                 字符连接

CONCAT_WS()          使用指定的分隔符进行连接

FORMAT()                  数字格式化

LOWER()                    转换成小写字母

UPPER()                     转换成大写字母

LEFT()                        获取左侧字符

RIGHT()                      获取右侧字符

字符函数分类

CONCAT()                 字符连接

CONCAT_WS()          使用指定的分隔符进行连接

FORMAT()                  数字格式化

LOWER()                    转换成小写字母

UPPER()                     转换成大写字母

LEFT()                        获取左侧字符

RIGHT()                      获取右侧字符

CONCAT()字符连接，数据表的名字后面可以加东西

例1

mysql> SELECT CONCAT('provinces','--');

+--------------------------+

| CONCAT('provinces','--') |

+--------------------------+

| provinces--              |

+--------------------------+

1 row in set (0.00 sec)

创建一张演示表

mysql> CREATE TABLE testwish(

    -> first_name VARCHAR(20) DEFAULT NULL,

    -> last_name VARCHAR(10)  DEFAULT NULL

    -> );

Query OK, 0 rows affected (0.01 sec)

mysql> SHOW COLUMNS FROM testwish;

+------------+-------------+------+-----+---------+-------+

| Field      | Type        | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| first_name | varchar(20) | YES  |     | NULL    |       |

| last_name  | varchar(10) | YES  |     | NULL    |       |

+------------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

插入一些记录

mysql> INSERT testwish(first_name,last_name) VALUES('QQ','121'),('AC','BD'),('121','725');

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM testwish;

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| QQ         | 121       |

| AC         | BD        |

| 121        | 725       |

+------------+-----------+

3 rows in set (0.00 sec)

例2

把这两个字段合并在一起输出，用AS起个别名

mysql> SELECT CONCAT(first_name,last_name) AS  fullname FROM testwish;

+----------+

| fullname |

+----------+

| QQ121    |

| ACBD     |

| 121725   |

+----------+

3 rows in set (0.00 sec)

CONCAT_WS()需要至少三个参数

例3，字符连接

mysql> SELECT CONCAT_WS('|','A','B','C');

+----------------------------+

| CONCAT_WS('|','A','B','C') |

+----------------------------+

| A|B|C                      |

+----------------------------+

1 row in set (0.00 sec)

mysql>

mysql> SELECT CONCAT_WS('-','test1','test2');

+--------------------------------+

| CONCAT_WS('-','test1','test2') |

+--------------------------------+

| test1-test2                    |

+--------------------------------+

1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS('-','test1','test2','test');

+---------------------------------------+

| CONCAT_WS('-','test1','test2','test') |

+---------------------------------------+

| test1-test2-test                      |

+---------------------------------------+

1 row in set (0.00 sec)

例4

截取小数点后两位数字

mysql> SELECT FORMAT(12580.45632,2);

+-----------------------+

| FORMAT(12580.45632,2) |

+-----------------------+

| 12,580.46             |

+-----------------------+

1 row in set (0.00 sec)

例5

大写转换成小写

mysql> SELECT LOWER('MySQL');

+----------------+

| LOWER('MySQL') |

+----------------+

| mysql          |

+----------------+

1 row in set (0.00 sec)

例6

小写转换成大写

mysql> SELECT UPPER('mysql');

+----------------+

| UPPER('mysql') |

+----------------+

| MYSQL          |

+----------------+

1 row in set (0.00 sec)

例7

获取左侧N个字符

mysql> SELECT LEFT('mysql',2);

+-----------------+

| LEFT('mysql',2) |

+-----------------+

| my              |

+-----------------+

1 row in set (0.00 sec)

例8 获取左侧N个字符，并转换为小写。用嵌套

mysql> SELECT LOWER(LEFT('MySQL',2));

+------------------------+

| LOWER(LEFT('MySQL',2)) |

+------------------------+

| my                     |

+------------------------+

1 row in set (0.00 sec)

例9

 获取右侧 N个字符

mysql> SELECT RIGHT('MySQL',2);

+------------------+

| RIGHT('MySQL',2) |

+------------------+

| QL               |

+------------------+

1 row in set (0.00 sec)

字符函数

LENGTH()              获取字符串长度

LTRIM()                  删除前导空格

RTRIM()                 删除后续空格 

TRIM()                   删除前导和后续空格

SUBSTRING()       字符串截取

[NOT]LIKE             模式匹配

PERLACE()          字符串替换

例10 获取字符串长度

mysql> SELECT LENGTH('MySQL');

+-----------------+

| LENGTH('MySQL') |

+-----------------+

|               5 |

+-----------------+

1 row in set (0.00 sec)

注意如果有空格，也会算在里面

mysql> SELECT LENGTH('  My  SQL');

+---------------------+

| LENGTH('  My  SQL') |

+---------------------+

|                   9 |

+---------------------+

1 row in set (0.00 sec)

前导空格，是第一个字符之前 的空格，后续空格，是最后一个字符之后的空格

例11，删除前导空格，并查看长度

mysql> SELECT LENGTH ('  MySQL    ');

+------------------------+

| LENGTH ('  MySQL    ') |

+------------------------+

|                     11 |

+------------------------+

1 row in set (0.00 sec)

mysql> SELECT LENGTH (LTRIM('  MySQL    '));

+-------------------------------+

| LENGTH (LTRIM('  MySQL    ')) |

+-------------------------------+

|                             9 |

+-------------------------------+

1 row in set (0.00 sec)

例12，删除 后续空格，并查看长度

mysql> SELECT LENGTH (RTRIM('  MySQL    '));

+-------------------------------+

| LENGTH (RTRIM('  MySQL    ')) |

+-------------------------------+

|                             7 |

+-------------------------------+

1 row in set (0.00 sec)

例13 去掉所有空格，并查看长度

mysql> SELECT LENGTH (TRIM('  MySQL    '));

+------------------------------+

| LENGTH (TRIM('  MySQL    ')) |

+------------------------------+

|                            5 |

+------------------------------+

1 row in set (0.00 sec)

例14 删除前导中的“?”

mysql> SELECT TRIM(LEADING '?' FROM '??MySQL???');

+-------------------------------------+

| TRIM(LEADING '?' FROM '??MySQL???') |

+-------------------------------------+

| MySQL???                            |

+-------------------------------------+

1 row in set (0.00 sec)

例15 删除后续中的”?”

mysql> SELECT TRIM(TRAILING '?' FROM '??MySQL???');

+--------------------------------------+

| TRIM(TRAILING '?' FROM '??MySQL???') |

+--------------------------------------+

| ??MySQL                              |

+--------------------------------------+

1 row in set (0.00 sec)

 例16 删除所有的”?”

mysql> SELECT TRIM(BOTH '?' FROM '??MySQL???');

+----------------------------------+

| TRIM(BOTH '?' FROM '??MySQL???') |

+----------------------------------+

| MySQL                            |

+----------------------------------+

1 row in set (0.00 sec)

注意BOTH 删除不掉中间的”?”

mysql> SELECT TRIM(BOTH '?' FROM '??My??SQL???');

+------------------------------------+

| TRIM(BOTH '?' FROM '??My??SQL???') |

+------------------------------------+

| My??SQL                            |

+------------------------------------+

1 row in set (0.00 sec)

例17用字符串替换掉字符串

mysql> SELECT REPLACE('??My??SQL????','?','--');

+-----------------------------------+

| REPLACE('??My??SQL????','?','--') |

+-----------------------------------+

| ----My----SQL--------             |

+-----------------------------------+

1 row in set (0.00 sec)

mysql> SELECT REPLACE('??My??SQL????','?','');

+---------------------------------+

| REPLACE('??My??SQL????','?','') |

+---------------------------------+

| MySQL                           |

+---------------------------------+

1 row in set (0.00 sec)

例18 用字符串替换掉字符串,一对多

mysql> SELECT REPLACE('??My??SQL????','?','-+-!!');

+-----------------------------------------------+

| REPLACE('??My??SQL????','?','-+-!!')          |

+-----------------------------------------------+

| -+-!!-+-!!My-+-!!-+-!!SQL-+-!!-+-!!-+-!!-+-!! |

+-----------------------------------------------+

1 row in set (0.00 sec)

例19用字符串替换掉字符串，多对一

mysql> SELECT REPLACE('??My??SQL????','??','~');

+-----------------------------------+

| REPLACE('??My??SQL????','??','~') |

+-----------------------------------+

| ~My~SQL~~                         |

+-----------------------------------+

1 row in set (0.00 sec)

例20，字符串截取，从第一位开始，截取两个

mysql> SELECT SUBSTRING('MySQL',1,2);

+------------------------+

| SUBSTRING('MySQL',1,2) |

+------------------------+

| My                     |

+------------------------+

1 row in set (0.00 sec)

注意不指定位置的情况

mysql> SELECT SUBSTRING('MySQL',3);

+----------------------+

| SUBSTRING('MySQL',3) |

+----------------------+

| SQL                  |

+----------------------+

1 row in set (0.00 sec)

例21 倒序截取

mysql> SELECT SUBSTRING('MySQL',-2);

+-----------------------+

| SUBSTRING('MySQL',-2) |

+-----------------------+

| QL                    |

+-----------------------+

1 row in set (0.00 sec)

模式的匹配,%表示0个或多个字符，‘1’表示TRUE 

mysql> SELECT 'MySQL' LIKE 'M%';

+-------------------+

| 'MySQL' LIKE 'M%' |

+-------------------+

|                 1 |

+-------------------+

1 row in set (0.00 sec)

例22  查找第一个字符组中包含‘Q’的用户 

mysql> SELECT * FROM testwish WHERE first_name LIKE '%Q%';

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| QQ         | 121       |

+------------+-----------+

1 row in set (0.00 sec)

当前表

mysql> SELECT *  FROM testwish;

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| QQ         | 121       |

| AC         | BD        |

| 121        | 725       |

| tom%       | 2016      |

+------------+-----------+

例子23查找 包含% 的用户

告诉字符串，1 后面的不需要再进行通配符解析，直接默认为是标准的%

mysql> SELECT * FROM testwish WHERE first_name LIKE '%1%%' ESCAPE '1';

+------------+-----------+

| first_name | last_name |

+------------+-----------+

| tom%       | 2016      |

+------------+-----------+