欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL字符函数的详细介绍

程序员文章站 2022-05-02 21:45:31
...
MySQL字符函数的详细介绍

MySQL字符函数的详细介绍


CONCAT()和CONCAT_WS()字符连接

mysql> SELECT CONCAT('mysql','5.6');
+-----------------------+
| CONCAT('mysql','5.6') |
+-----------------------+
| mysql5.6              |
+-----------------------+
1 row in set (0.09 sec)

mysql> SELECT CONCAT('mysql','-','5.6');
+---------------------------+
| CONCAT('mysql','-','5.6') |
+---------------------------+
| mysql-5.6                 |
+---------------------------+
1 row in set (0.00 sec)


例如:将用户的first_name和last_name连接起来成一个字符串

mysql> SELECT * FROM tdb_test;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | A          | B         |
|  2 | Jack       | Bob       |
|  3 | tom%       | 123       |
+----+------------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT CONCAT(first_name,last_name) AS fullname FROM tdb_test;
+----------+
| fullname |
+----------+
| AB       |
| JackBob  |
| tom%123  |
+----------+
3 rows in set (0.00 sec)


CONCAT_WS()的第一个参数为分隔符,后面的为要连接的字符

mysql> SELECT CONCAT_WS('|','A','B','C');
+----------------------------+
| CONCAT_WS('|','A','B','C') |
+----------------------------+
| A|B|C                      |
+----------------------------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT_WS('-','mysql','5.6');
+------------------------------+
| CONCAT_WS('-','mysql','5.6') |
+------------------------------+
| mysql-5.6                    |
+------------------------------+
1 row in set (0.00 sec)


FORMAT()数字格式化

mysql> SELECT FORMAT(12560.75,1);
+--------------------+
| FORMAT(12560.75,1) |
+--------------------+
| 12,560.8           |
+--------------------+
1 row in set (0.01 sec)

LOWER()和 UPPER()
mysql> SELECT LOWER('MySql');
+----------------+
| LOWER('MySql') |
+----------------+
| mysql          |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT UPPER('MySql');
+----------------+
| UPPER('MySql') |
+----------------+
| MYSQL          |
+----------------+
1 row in set (0.00 sec)


LEFT()和RIGHT()

分别有两个参数,第一个为所用的字符串,第二个为取几位

例如获取MySQL的前两位

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


例如获取MySQL的后两位

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


LENGTH获取字符串长度

mysql> SELECT LENGTH('MySQL');
+-----------------+
| LENGTH('MySQL') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.02 sec)


LTRIM,RTRIM,TRIM

比如直接用SELECT TRIM(' mysql ');

将会删除mysql的前和后的空格,LTRIM只删除左侧的空格,RTRIM只删除右侧的空格。


其他用法:利用TRIM()删除左右两侧的特定字符。

例如:

mysql> SELECT TRIM(LEADING '?' FROM '??MySQL???');
+-------------------------------------+
| TRIM(LEADING '?' FROM '??MySQL???') |
+-------------------------------------+
| MySQL???                            |
+-------------------------------------+
1 row in set (0.02 sec)
mysql> SELECT TRIM(TRAILING '?' FROM '??MySQL???');
+--------------------------------------+
| TRIM(TRAILING '?' FROM '??MySQL???') |
+--------------------------------------+
| ??MySQL                              |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TRIM(BOTH '?' FROM '??MySQL???');
+----------------------------------+
| TRIM(BOTH '?' FROM '??MySQL???') |
+----------------------------------+
| MySQL                            |
+----------------------------------+
1 row in set (0.01 sec)

注:TRIM()只能删除前导和后续的,不能删除字符中间的,比如TRIM('My SQL');这个空格就删不掉。

REPLACE()字符串替换

例如将??My??SQL???中的?替换成空

mysql> SELECT REPLACE('??My??SQL???','?','');
+--------------------------------+
| REPLACE('??My??SQL???','?','') |
+--------------------------------+
| MySQL                          |
+--------------------------------+
1 row in set (0.00 sec)


SUBSTRING()字符串截取

所含参数,从第几位截,截取几位。

mysql> SELECT SUBSTRING('MySQL',1,2);
+------------------------+
| SUBSTRING('MySQL',1,2) |
+------------------------+
| My                     |
+------------------------+
1 row in set (0.00 sec)


需要注意的是,和编程语言不同的是,第一位是1,不是0。

如果只有从第几位起,没有截取几个,就会,一直截到最后。

mysql> SELECT SUBSTRING('MySQL',3);
+----------------------+
| SUBSTRING('MySQL',3) |
+----------------------+
| SQL                  |
+----------------------+
1 row in set (0.00 sec)


位置也可以为负值(倒着数)

mysql> SELECT SUBSTRING('MySQL',-1);
+-----------------------+
| SUBSTRING('MySQL',-1) |
+-----------------------+
| L                     |
+-----------------------+
1 row in set (0.04 sec)


注,只能是起始位置可以有负值,但是截取长度不能有负值。

LIKE模式匹配(常用于查询中)

mysql> SELECT 'MySQL' LIKE 'M%';
+-------------------+
| 'MySQL' LIKE 'M%' |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)


%是指任意一位或者多位

mysql> SELECT * FROM tdb_test;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | A          | B         |
|  2 | Jack       | Bob       |
|  3 | tom%       | 123       |
+----+------------+-----------+
3 rows in set (0.00 sec)


例如查询,姓名中包含o的用户

mysql> SELECT * FROM tdb_test WHERE first_name LIKE '%o%';
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  3 | tom%       | 123       |
+----+------------+-----------+
1 row in set (0.00 sec)


例如查询,姓名中包含%的用户

mysql> SELECT * FROM tdb_test WHERE first_name LIKE '%%%';
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | A          | B         |
|  2 | Jack       | Bob       |
|  3 | tom%       | 123       |
+----+------------+-----------+
3 rows in set (0.00 sec)


发现显示的是所有查询结果。因为mysql会认为上述的%都为通配符。正确操作如下

mysql> SELECT * FROM tdb_test WHERE first_name LIKE '%1%%' ESCAPE '1';
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  3 | tom%       | 123       |
+----+------------+-----------+
1 row in set (0.00 sec)


注:%:任意个字符.

_:任意一个字符。

以上就是MySQL字符函数的详细介绍的内容,更多相关内容请关注PHP中文网(www.php.cn)!

相关标签: MySQL,字符函数