MySQL字符函数的详细介绍
程序员文章站
2022-05-02 21:45:31
...
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)!
推荐阅读
-
jquery中map函数与each函数的区别实例教程介绍
-
PHP函数preg_match_all正则表达式的基本使用详细解析
-
MySQL的时间差函数TIMESTAMPDIFF、DATEDIFF的用法
-
MySQL的时间差函数(TIMESTAMPDIFF、DATEDIFF)、日期转换计算函数(date_add、day、date_format、str_to_date)
-
详谈mysql order by in 的字符顺序(推荐)
-
Remoting和Webservice的详细介绍及区别
-
Shell函数的7种用法介绍
-
Shell中的函数、函数定义、作用域问题介绍
-
如何使用简单百宝箱 简单百宝箱的详细使用步骤介绍
-
C#算法函数:获取一个字符串中的最大长度的数字