字符串函数的使用解析
- 1 计算字符串字符数的函数和字符串长的函数
- 2 合并字符串函数 CONCAT(s1,s2,...)、CONCAT_WS(xs1,s2,...)
- 3 替换字符串函数INSERT(s1,x,len,s2)
- 4 字母大小写转换函数
- 5 获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)
- 6 填充字符串的函数 LPAD(s1,len,s2)和 RPAD(s1,len,s2)
- 7 删除空格的函数 LTRIMs)、RTRIMs)和 TRIM(s)
- 8 删除指定字符串的函数TRIM(S1 FROM s)
- 9 重复生成字符串的函数 REPEAT(s,n)
- 10 空格函数 SPACE(n)和替换函数 REPLACE(s,s1,s2)
- 11 比较字符串大小的函数 STRCMP(s1,s2)
- 12 获取子串的函数 SUBSTRING(s,n,len)和 MID(s,n,len)
- 13 匹配子串开始位置的函数
- 14 字符串逆序的函数 REVERSE(S)
- 15 返回指定位置的字符串的函数
- 16 返回指定字符串位置的函数 FIELD(s,s1,s2,...,sn)
- 17 返回子串位置的函数 FIND IN SET(s1,s2)
- 18 选取字符串的函数 MAKE SET(x,s1,s2,...,sn)
字符串函数主要用来处理数据库中的字符串数据。MySQL中的字符串函数有计算字符串长度函数、字符串合并函数、字符串替换函数、字符串比较函数、查找指定字符串位置函数等。本节将介绍各种字符串函数的功能和用法。
1 计算字符串字符数的函数和字符串长的函数
CHAR_LENGTH(str)返回值为字符串str所包含的字符个数,一个多字节字符算作资格单字符。
mysql> SELECT CHAR_LENGTH('date'), CHAR_LENGTH('1234'),CHAR_LENGTH('五四三二一');
+---------------------+---------------------+--------------------------------+
| CHAR_LENGTH('date') | CHAR_LENGTH('1234') | CHAR_LENGTH('五四三二一') |
+---------------------+---------------------+--------------------------------+
| 4 | 4 | 5 |
+---------------------+---------------------+--------------------------------+
1 row in set (0.00 sec)
LENGTH(str)返回值为字符串的字节长度,UTF8的编码字符集是,一个汉字是三个字节,一个字母或者数字是一个字节。
mysql> SELECT LENGTH('date'), LENGTH('1234'),LENGTH('五四三二一');
+----------------+----------------+---------------------------+
| LENGTH('date') | LENGTH('1234') | LENGTH('五四三二一') |
+----------------+----------------+---------------------------+
| 4 | 4 | 15 |
+----------------+----------------+---------------------------+
1 row in set (0.00 sec)
2 合并字符串函数 CONCAT(s1,s2,…)、CONCAT_WS(xs1,s2,…)
CONCAT(s1,s2,…)返回结果为连接参数产生的字符串,一个或者多个参数。任意一个参数为NULL,则返回值为NULL,所有参数为非二进制字符串,结果为非二进制字符串,如果任意一个参数为二进制字符串,则结果为一个二进制字符串。
mysql> SELECT CONCAT('My SQL', '8.0'),CONCAT('My',NULL, 'SQL');
+-------------------------+--------------------------+
| CONCAT('My SQL', '8.0') | CONCAT('My',NULL, 'SQL') |
+-------------------------+--------------------------+
| My SQL8.0 | NULL |
+-------------------------+--------------------------+
1 row in set (0.00 sec)
CONCAT_WS(xs1,s2,…) 类似于 CONCAT 函数,但是使用了一个分隔符来连接多个字符串。xs1表示使用分隔符号,分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其他参数。如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。
mysql> SELECT CONCAT_WS('--', '1st','2nd', '3rd'), CONCAT_WS('-*-', '1st', NULL, '3rd');
+-------------------------------------+--------------------------------------+
| CONCAT_WS('--', '1st','2nd', '3rd') | CONCAT_WS('-*-', '1st', NULL, '3rd') |
+-------------------------------------+--------------------------------------+
| 1st--2nd--3rd | 1st-*-3rd |
+-------------------------------------+--------------------------------------+
1 row in set (0.00 sec)
3 替换字符串函数INSERT(s1,x,len,s2)
INSERT(s1,x,len,s2)返回字符串s1,其子字符串起始于x位置和被字符串s2取代的len字符。如果x超过字符串长度,则返回为原始字符串,如果len长度大于其他字符串长度,则从位置x开始替换,如果任意一个参数为NULL,则返回结果为NULL。
mysql> SELECT INSERT('qwerty', 2, 4, 'What') AS col1,
-> INSERT('qwerty', -1, 3, 'What') AS col2,
-> INSERT('qwerty123', 2, 100, 'What') AS col3;
+--------+--------+-------+
| col1 | col2 | col3 |
+--------+--------+-------+
| qWhaty | qwerty | qWhat |
+--------+--------+-------+
1 row in set (0.00 sec)
4 字母大小写转换函数
LOWER(str)或者LCASE(str)可以将字符串str 中的字母字符全部转换成小写字母。
mysql> SELECT LOWER('AABBCCDD'), LCASE('qqWWeeRRTt');
+-------------------+---------------------+
| LOWER('AABBCCDD') | LCASE('qqWWeeRRTt') |
+-------------------+---------------------+
| aabbccdd | qqwweerrtt |
+-------------------+---------------------+
1 row in set (0.00 sec)
UPPER(str)或者UCASE(str)可以将字符串str 中的字母字符全部转换成大写字母。
mysql> SELECT UPPER('abcdefg'), UCASE('AaBbCcDd');
+------------------+-------------------+
| UPPER('abcdefg') | UCASE('AaBbCcDd') |
+------------------+-------------------+
| ABCDEFG | AABBCCDD |
+------------------+-------------------+
1 row in set (0.00 sec)
5 获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)
LEFT(s,n) 返回字符串S开始最左边的n个字符串,RIGHT(s,n)返回字符串S开始最右边的n个字符串。
mysql> SELECT LEFT('1111122222', 5),RIGHT('1111122222', 5);
+-----------------------+------------------------+
| LEFT('1111122222', 5) | RIGHT('1111122222', 5) |
+-----------------------+------------------------+
| 11111 | 22222 |
+-----------------------+------------------------+
1 row in set (0.00 sec)
6 填充字符串的函数 LPAD(s1,len,s2)和 RPAD(s1,len,s2)
LPAD(sl,len,s2)返回字符串 s1,其左边由字符串 s2 填补到 len 字符长度。假如 s1 的长度大于len,则返回值被缩短至 len 字符。
mysql> SELECT LPAD('123456',4,'??'), LPAD('BBBB',10,'??');
+-----------------------+----------------------+
| LPAD('123456',4,'??') | LPAD('BBBB',10,'??') |
+-----------------------+----------------------+
| 1234 | ??????BBBB |
+-----------------------+----------------------+
1 row in set (0.00 sec)
RPAD(sl,len,s2)返回字符串 sl,其右边被字符串 s2 填补至 len 字符长度。假如字符串 sI 的长度大于len,则返回值被缩短到len字符长度。
mysql> SELECT RPAD('123456',4,'??'), RPAD('BBBB',10,'??');
+-----------------------+----------------------+
| RPAD('123456',4,'??') | RPAD('BBBB',10,'??') |
+-----------------------+----------------------+
| 1234 | BBBB?????? |
+-----------------------+----------------------+
1 row in set (0.00 sec)
7 删除空格的函数 LTRIMs)、RTRIMs)和 TRIM(s)
LTRIM(s)返回字符串s,字符串左侧空格字符被删除。
mysql> SELECT '( aaaa )',CONCAT('(',LTRIM(' bbbb '),')');
+------------+-----------------------------------+
| ( aaaa ) | CONCAT('(',LTRIM(' bbbb '),')') |
+------------+-----------------------------------+
| ( aaaa ) | (bbbb ) |
+------------+-----------------------------------+
1 row in set (0.00 sec)
RTRIM(s)返回字符串s,字符串右侧空格字符被删除。
mysql> SELECT '( bbbb )',CONCAT('(', RTRIM (' cccc '),')');
+------------+-------------------------------------+
| ( bbbb ) | CONCAT('(', RTRIM (' cccc '),')') |
+------------+-------------------------------------+
| ( bbbb ) | ( cccc) |
+------------+-------------------------------------+
1 row in set (0.00 sec)
TRIM(s)删除字符串s两侧的空格。
mysql> SELECT '( dddd )',CONCAT('(', TRIM(' wwww '),')');
+------------+-----------------------------------+
| ( dddd ) | CONCAT('(', TRIM(' wwww '),')') |
+------------+-----------------------------------+
| ( dddd ) | (wwww) |
+------------+-----------------------------------+
1 row in set (0.00 sec)
8 删除指定字符串的函数TRIM(S1 FROM s)
TRIM(sI FROM s)删除字符串s 中两端所有的子字符串 s1。s1为可选项,在未指定情况下,删除空格。
mysql> SELECT TRIM('123' FROM '1234567891234561234123') ;
+-------------------------------------------+
| TRIM('123' FROM '1234567891234561234123') |
+-------------------------------------------+
| 4567891234561234 |
+-------------------------------------------+
1 row in set (0.00 sec)
9 重复生成字符串的函数 REPEAT(s,n)
REPEAT(s,n)返回一个由重复的字符串 s 组成的字符串,字符串 s 的数目等于n。若n<=0,则返回一个空字符串。若s或n为NULL,则返回NULL。
mysql> SELECT REPEAT('1QAZ', 3);
+-------------------+
| REPEAT('1QAZ', 3) |
+-------------------+
| 1QAZ1QAZ1QAZ |
+-------------------+
1 row in set (0.00 sec)
10 空格函数 SPACE(n)和替换函数 REPLACE(s,s1,s2)
SPACE(n)返回一个由n个空格组成的字符串。
mysql> SELECT CONCAT('(', SPACE(12), ')' );
+------------------------------+
| CONCAT('(', SPACE(12), ')' ) |
+------------------------------+
| ( ) |
+------------------------------+
1 row in set (0.00 sec)
REPLACE(s.s1,s2)使用字符串 s2 替代字符串s中所有的字符串 s1。
mysql> SELECT REPLACE('xxx.zxc.bxc.aaa', 'x', 'w');
+--------------------------------------+
| REPLACE('xxx.zxc.bxc.aaa', 'x', 'w') |
+--------------------------------------+
| www.zwc.bwc.aaa |
+--------------------------------------+
1 row in set (0.00 sec)
11 比较字符串大小的函数 STRCMP(s1,s2)
STRCMP(s1.s2): 若所有的字符串均相同,则返回0: 若根据当前分类次序,第一个参数小于第二个,则返回-1;其他情况返回 1。
mysql> SELECT STRCMP('qwer', 'qwer2'),STRCMP('qwer2', 'qwer'), STRCMP('qwer', 'qwer');
+-------------------------+-------------------------+------------------------+
| STRCMP('qwer', 'qwer2') | STRCMP('qwer2', 'qwer') | STRCMP('qwer', 'qwer') |
+-------------------------+-------------------------+------------------------+
| -1 | 1 | 0 |
+-------------------------+-------------------------+------------------------+
1 row in set (0.00 sec)
12 获取子串的函数 SUBSTRING(s,n,len)和 MID(s,n,len)
SUBSTRING(s.n,len)带有 len 参数的格式,从字符串s 返回一个长度与 len 字符相同的子字符串,起始于位置 n。也可能对 n 使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的n字符,即倒数第n个字符,而不是字符串的开头位置。
mysql> SELECT SUBSTRING('database',5) AS col1,
-> SUBSTRING('database',5,3) AS col2,
-> SUBSTRING('monkey', -3) AS col3,
-> SUBSTRING('monkeyzoo', -5, 3) AS col4;
+------+------+------+------+
| col1 | col2 | col3 | col4 |
+------+------+------+------+
| base | bas | key | eyz |
+------+------+------+------+
1 row in set (0.00 sec)
MID(s,n,len)与 SUBSTRING(s,n,len)的作用相同。
mysql> SELECT MID('breakfast',5) as col1,
-> MID('breakfast',5,3) as col2,
-> MID('lunch', -3) as col3,
-> MID('lunch', -5, 3) as col4;
+-------+------+------+------+
| col1 | col2 | col3 | col4 |
+-------+------+------+------+
| kfast | kfa | nch | lun |
+-------+------+------+------+
1 row in set (0.00 sec)
13 匹配子串开始位置的函数
LOCATE(strl.str)、POSITION(strlIN str)和INSTR(str,strl)3 个函数的作用相同,返回子字符串 str1 在字符串str 中的开始位置。
mysql> SELECT LOCATE('ball','basketball'),POSITION('ball'IN 'ball'),INSTR ('football', 'ball');
+-----------------------------+---------------------------+----------------------------+
| LOCATE('ball','basketball') | POSITION('ball'IN 'ball') | INSTR ('football', 'ball') |
+-----------------------------+---------------------------+----------------------------+
| 7 | 1 | 5 |
+-----------------------------+---------------------------+----------------------------+
1 row in set (0.00 sec)
14 字符串逆序的函数 REVERSE(S)
REVERSE(s)将字符串s 反转,返回的字符串的顺序和s 字符串顺序相反。
mysql> SELECT REVERSE('abc123');
+-------------------+
| REVERSE('abc123') |
+-------------------+
| 321cba |
+-------------------+
1 row in set (0.00 sec)
15 返回指定位置的字符串的函数
ELT(N,字符串 1,字符串 2,字符串 3,,字符串 N): 若 N=1,则返回值为字符串 1; 若 N=2则返回值为字符串 2:以此类推:若 N 小于1或大于参数的数目,则返回值为 NULL。
mysql> SELECT ELT(3,'1st','2nd','3rd'), ELT(3,'net','os');
+--------------------------+-------------------+
| ELT(3,'1st','2nd','3rd') | ELT(3,'net','os') |
+--------------------------+-------------------+
| 3rd | NULL |
+--------------------------+-------------------+
1 row in set (0.00 sec)
16 返回指定字符串位置的函数 FIELD(s,s1,s2,…,sn)
FIELD(s,sl,s2,…,sn)返回字符串 s 在列表 s1,s2,…,sn 中第一次出现的位置,在找不到 s 的情况下,返回值为 0。如果s为 NULL,则返回值为 0,原因是 NULL 不能同任何值进行同等比较。
mysql> SELECT FIELD('Hi', 'hihi', 'Hey', 'Hi', 'bas') as col1,
-> FIELD('Hi', 'Hey', 'Lo', 'Hilo', 'foo') as col2;
+------+------+
| col1 | col2 |
+------+------+
| 3 | 0 |
+------+------+
1 row in set (0.00 sec)
17 返回子串位置的函数 FIND IN SET(s1,s2)
FIND IN SET(sl,s2)返回字符串 s1 在字符串列表 s2中出现的位置,字符串列表是一个由多个逗号“,’分开的字符串组成的列表。如果 s1 不在 s2 或 s2 为空字符串,则返回值为 0。如果任意一个参数为 NULL,则返回值为 NULL。这个函数在第一个参数包含一个逗号“’时将无法正常运行。
mysql> SELECT FIND_IN_SET('Hi','hihi,Hey,Hi,bas');
+-------------------------------------+
| FIND_IN_SET('Hi','hihi,Hey,Hi,bas') |
+-------------------------------------+
| 3 |
+-------------------------------------+
1 row in set (0.00 sec)
18 选取字符串的函数 MAKE SET(x,s1,s2,…,sn)
MAKE SET(x,sl,s2…,sn)函数按 x 的二进制数从 sl,s2…sn 中选取字符串。例如 5 的二进制是0101,这个二进制从右往左的第 1位和第 3 位是 1,所以选取 1和 s3。sl,s2…sn 中的 NULL值不会被添加到结果中。
mysql> SELECT MAKE_SET(1,'a','b','c') as col1,
-> MAKE_SET(1 | 4,'hello','nice','world') as col2,
-> MAKE_SET(1 | 4,'hello','nice',NULL,'world') as col3,
-> MAKE_SET(0,'a','b','c') as col4;
+------+-------------+-------+------+
| col1 | col2 | col3 | col4 |
+------+-------------+-------+------+
| a | hello,world | hello | |
+------+-------------+-------+------+
1 row in set (0.00 sec)