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()字符串替换
例如将mysql?中的？替换成空
mysql> select replace('mysql?','?',''); +--------------------------------+ | replace('mysql?','?','') | +--------------------------------+ | 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字符函数的详细介绍的内容。