10.MySQL字符函数

字符函数

连接字符：CONCAT()

mysql> SELECT CONCAT('www.','toly.','top');
+------------------------------+
| CONCAT('www.','toly.','top') |
+------------------------------+
| www.toly.top                 |
+------------------------------+
1 row in set (0.00 sec)

创建名单表并插入数据：

mysql> CREATE TABLE 名单 (
    -> 姓 VARCHAR(12) NOT NULL,
    -> 名  VARCHAR(24) NOT NULL
    -> );
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT 名单 VALUES('张','飞'),('刘','备'),('关','羽');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT *FROM 名单;
+-----+-----+
| 姓  | 名  |
+-----+-----+
| 张  | 飞  |
| 刘  | 备  |
| 关  | 羽  |
+-----+-----+
3 rows in set (0.00 sec)

全名拼接输出：

mysql> SELECT CONCAT(姓,名)  AS 三英 FROM 名单;
+--------+
| 三英   |
+--------+
| 张飞   |
| 刘备   |
| 关羽   |
+--------+
3 rows in set (0.00 sec)

指定分隔符连接字符：CONCAT_WS()

mysql> SELECT CONCAT_WS('.','www','toly','top');
+-----------------------------------+
| CONCAT_WS('.','www','toly','top') |
+-----------------------------------+
| www.toly.top                      |
+-----------------------------------+
1 row in set (0.00 sec)

格式化数字：FORMAT()

mysql> SELECT FORMAT(1299402.779040,2);
+--------------------------+
| FORMAT(1299402.779040,2) |
+--------------------------+
| 1,299,402.78             |
+--------------------------+
1 row in set (0.02 sec)

变小写：LOWER()

mysql> SELECT LOWER('ToLy');
+---------------+
| LOWER('ToLy') |
+---------------+
| toly          |
+---------------+
1 row in set (0.00 sec)

变大写：UPPER()

mysql> SELECT UPPER('ToLy');
+---------------+
| UPPER('ToLy') |
+---------------+
| TOLY          |
+---------------+
1 row in set (0.00 sec)

从左侧取字符：LEFT()

mysql> SELECT LEFT('ToLy',2);
+----------------+
| LEFT('ToLy',2) |
+----------------+
| To             |
+----------------+
1 row in set (0.00 sec)

从左侧取字符：LEFT()

mysql> SELECT RIGHT('ToLy',2);
+-----------------+
| RIGHT('ToLy',2) |
+-----------------+
| Ly              |
+-----------------+
1 row in set (0.00 sec)

字符长度(注:含空格)：LENGTH()

mysql> SELECT LENGTH('To Ly');
+-----------------+
| LENGTH('To Ly') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

删开头左空格:LTRIM()

mysql> SELECT LTRIM('  To Ly  ');
+--------------------+
| LTRIM('  To Ly  ') |
+--------------------+
| To Ly              |
+--------------------+
1 row in set (0.00 sec)

删结尾右空格:LTRIM()

mysql> SELECT RTRIM('  To Ly  ');
+--------------------+
| RTRIM('  To Ly  ') |
+--------------------+
|   To Ly            |
+--------------------+
1 row in set (0.00 sec)

删开头左空格和结尾右空格:TRIM()

mysql> SELECT TRIM('  To Ly  ');
+-------------------+
| TRIM('  To Ly  ') |
+-------------------+
| To Ly             |
+-------------------+
1 row in set (0.00 sec)

替换：REPLACE()

mysql> SELECT REPLACE('  To Ly  '," ","");
+-----------------------------+
| REPLACE('  To Ly  '," ","") |
+-----------------------------+
| ToLy                        |
+-----------------------------+
1 row in set (0.00 sec)

截取:SUBSTRING(“字符串”,第几个开始(左正右负),截取几个)

mysql> SELECT SUBSTRING("ToLy",2,1);
+-----------------------+
| SUBSTRING("ToLy",2,1) |
+-----------------------+
| o                     |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING("ToLy",-2,2);
+------------------------+
| SUBSTRING("ToLy",-2,2) |
+------------------------+
| Ly                     |
+------------------------+
1 row in set (0.00 sec)

匹配字符：LIKE()(%:任意字符 _:任一字符)

mysql> INSERT 名单 VALUES('诸葛','亮');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT *FROM 名单;
+--------+-----+
| 姓     | 名  |
+--------+-----+
| 张     | 飞  |
| 刘     | 备  |
| 关     | 羽  |
| 诸葛   | 亮  |
+--------+-----+
4 rows in set (0.00 sec)

mysql> SELECT*FROM 名单 WHERE 姓 LIKE('%葛%') ;
+--------+-----+
| 姓     | 名  |
+--------+-----+
| 诸葛   | 亮  |
+--------+-----+
1 row in set (0.00 sec)

如果已经有’%’:(其中0可任意指定，与后统一即可)

mysql> INSERT 名单 VALUES('赵%','云');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT*FROM 名单 WHERE 姓 LIKE('%0%%') ESCAPE '0';
+------+-----+
| 姓   | 名  |
+------+-----+
| 赵%  | 云  |
+------+-----+
1 row in set (0.00 sec)