MySQL字符串函数

字符串函数

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;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to 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)