©2020-2024 ioDraw All rights reserved
<>字符串函数
<>length()
求字符串的长度
注意 若为null时,如果不加单引号,则返回null,若加单引号,则返回字符的长度.
mysql> select length(@de) -> ; +-------------+ | length(@de) | +-------------+
| 19 | +-------------+ 1 row in set (0.00 sec) mysql> select @de -> ; +
---------------------+ | @de | +---------------------+ | 2022-06-02 14:49:27 | +
---------------------+ 1 row in set (0.00 sec) mysql> select length('hello
every'); +-----------------------+ | length('hello every') | +
-----------------------+ | 11 | +-----------------------+ 1 row in set (0.00 sec
)
<>concat()
合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
参数
若有任何一个参数为 NULL，则返回值为
NULL。若所有参数均为非二进制字符串，则结果为非二进制字符串。若自变量中含有任一二进制字符串，则结果为一个二进制字符串。
实例
mysql> select concat('hello',' world'); +---------------------------+ | concat(
'hello',' world') | +---------------------------+ | hello world | +
---------------------------+ 1 row in set (0.00 sec) mysql> select concat(
'hello',3,'i world'); +------------------------------+ | concat('hello',3,'i
world') | +------------------------------+ | hello3i world | +
------------------------------+ 1 row in set (0.00 sec)
<>insert()
替换字符串
参数
insert(s1,x,len,s2)
替换字符串函数 INSERT(s1，x，len，s2) 返回字符串 s1，子字符串起始于 x 位置，并且用 len 个字符长的字符串代替 s2
若 x 超过字符串长度，则返回值为原始字符串。假如 len 的长度大于其他字符串的长度，则从位置 x 开始替换。若任何一个参数为 NULL，则返回值为
NULL。
mysql> select insert('hello',2,2,'world') as c1; +----------+ | c1 | +
----------+ | hworldlo | +----------+ 1 row in set (0.00 sec) mysql> select
insert('hello',2,6,'world') as c2; +--------+ | c2 | +--------+ | hworld | +
--------+ 1 row in set (0.00 sec) mysql> select insert('hello',2,'world') as c3;
ERROR1064 (42000): You have an error in your SQL syntax; check the manual that
correspondsto your MySQL server version for the right syntax to use near ') as
c3' at line 1 mysql> select insert('hello',-1,3,'world') as c4; +-------+ | c4 |
+-------+ | hello | +-------+ 1 row in set (0.00 sec) mysql> select insert(
'hello',-4,3,'world') as c5; +-------+ | c5 | +-------+ | hello | +-------+ 1
row in set (0.00 sec)
<>replace()
替换字符串
参数
replace(s,s1,s2)
使用字符串 s2 替换字符串 s 中所有的字符串 s1
mysql> select replace('hello world','o','k'); +
--------------------------------+ | replace('hello world','o','k') | +
--------------------------------+ | hellk wkrld | +
--------------------------------+ 1 row in set (0.00 sec) mysql> select replace(
'hello world','o','null'); +-----------------------------------+ | replace(
'hello world','o','null') | +-----------------------------------+ | hellnull
wnullrld| +-----------------------------------+ 1 row in set (0.00 sec) mysql>
select replace('hello world','o',null); +---------------------------------+ |
replace('hello world','o',null) | +---------------------------------+ | NULL | +
---------------------------------+ 1 row in set (0.00 sec)
<>substr()
截取字符串
参数
SUBSTRING(s，n，len)
*
其中len可以省略,当省略时,表示从指定的截取位置开始,至到最后。
*
从字符串 s 返回一个长度同 len 字符相同的子字符串，起始于位置 n。
*
当n 为负值,则子字符串的位置起始于字符串结尾的第 n 个字符，即倒数第 n 个字符，而不是字符串的开头位置。
mysql> select substr('hello world',2,4); +---------------------------+ | substr
('hello world',2,4) | +---------------------------+ | ello | +
---------------------------+ 1 row in set (0.00 sec) mysql> select substr(
'hello world',2); +-------------------------+ | substr('hello world',2) | +
-------------------------+ | ello world | +-------------------------+ 1 row in
set (0.00 sec) mysql> select substr('hello world',-1,4); +
----------------------------+ | substr('hello world',-1,4) | +
----------------------------+ | d | +----------------------------+ 1 row in set
(0.00 sec) mysql> select substr('hello world',2,10); +
----------------------------+ | substr('hello world',2,10) | +
----------------------------+ | ello world | +----------------------------+ 1
row in set (0.00 sec)
<>strm()
去除字符串两边的空格
mysql> select concat('[[',trim(' hello world '),']]'); +
---------------------------------------------+ | concat('[[',trim(' hello world
'),']]') | +---------------------------------------------+ | [[hello world]] | +
---------------------------------------------+ 1 row in set (0.00 sec)
<>reverse()
字符串反转
mysql> select reverse('hello'); +------------------+ | reverse('hello') | +
------------------+ | olleh | +------------------+ 1 row in set (0.00 sec
<>left(s1,len)
从字符串左侧开始,截取字符串
当为负数时,返回空
mysql> select left('hello',4) -> ; +-----------------+ | left('hello',4) | +
-----------------+ | hell | +-----------------+ 1 row in set (0.00 sec) mysql>
select left('hello',6); +-----------------+ | left('hello',6) | +
-----------------+ | hello | +-----------------+ 1 row in set (0.00 sec) mysql>
select left('hello',-1); +------------------+ | left('hello',-1) | +
------------------+ | | +------------------+ 1 row in set (0.00 sec)
<>right(s1,len)
从右侧开始截取字符串
mysql> select right('hello',1) -> ; +------------------+ | right('hello',1) | +
------------------+ | o | +------------------+ 1 row in set (0.00 sec) mysql>
select right('hello',6) -> ; +------------------+ | right('hello',6) | +
------------------+ | hello | +------------------+ 1 row in set (0.00 sec) mysql
> select right('hello',-1); +-------------------+ | right('hello',-1) | +
-------------------+ | | +-------------------+ 1 row in set (0.00 sec)
<>lower()
将字符串全部转化为小写
mysql> select lower('HELLO WoRlD'); +----------------------+ | lower('HELLO
WoRlD') | +----------------------+ | hello world | +----------------------+ 1
row in set (0.00 sec)
<>upper()
将字符创全部转化为大写
mysql> select upper('hello world'); +----------------------+ | upper('hello
world') | +----------------------+ | HELLO WORLD | +----------------------+ 1
row in set (0.00 sec)