13.3 字符串函数
字符串函数是MySQL中最常用的一类函数。字符串函数主要用于处理表中的字符串。字符串函数包括求字符串长度、合并字符串、在字符串中插入字串和大小写字母之间的切换等函数。下面是各种字符串函数的符号、作用。
<td width="30%" rtrim(s)<="" td="">
| 函数 | 作用 |
|---|---|
| CHAR_LIGNTH(s) | 返回字符串s的字符数 |
| LENGTH(s) | 返回字符串s的长度 |
| CONCAT(s1,s2,...) | 将字符串s1,s2等合并成为一个字符串 |
| CONCAT_WS(x,s1,s2,...) | 同CONCAT(s1,s2,...)函数，但是把每个字符串直接要加上x |
| INSERT(s1,x,len,s2) | 将字符串s2替换s1的x位置开始长度为len的字符串 |
| UPPER(s),UCASE(s) | 将字符串的所有字母都变成大写字母 |
| LOWWER(s),LCASE(s) | 将字符串的所有字母都变成小写字母 |
| LEFT(s,n) | 返回字符串的前n个字符 |
| RIGHT(s,n) | 返回字符串s的后n个字符 |
| LPAD(s1,len,s2) | 字符串s2来填充s1的结尾开始处，是字符串长度大道len |
| RPAD(s1,len,s2) | 字符串s2来填充s1的结尾处，是字符串长度大道len |
| LTRIM(s)/td> | 去掉字符串开始处的空格 |
| 去掉字符串结尾处的空格 | |
| TRIM(s) | 去掉字符串s开始处和结尾处的空格 |
| TRIM(s1 FROM s) | 去掉字符串s中开始处和结尾处的字符串s1 |
| REPEAT(s,n) | 将字符串s重复n次 |
| SPACE(n) | 返回n分空格 |
| REPLACE(s,s1,s2) | 用字符串s2替代字符串s中的字符串s1 |
| STRCMP(s1,s2) | 比较字符串s1和s2 |
| SUBSTRING(s,n,len) | 获取从字符串s中的第n个位置开始长度为len的字符串 |
| MID(s,n,len) | 同SUBSTRING(s,n,len) |
| LOCATE(s1,s)POSITION(s1 IN s) | 从s中获取s1的开始位置 |
| INSERT(s,s1) | 从s中获取s1的开始位置 |
| REVERSE(s) | 将字符串s的顺序反过来 |
| ELT(n,s1,s2...) | 返回第n个字符串 |
| EXPORT_SET(x,s1,s2) | |
| FIELD(s,s1,s2,...) | 返回第一个与字符串s匹配的字符串的位置 |
| FIND_IN_SET(s1,s2) | 返回在字符串s2中与s1匹配的字符串的位置 |
| MALE_SET(x,s1,s2,...) | 按x的二进制数从s1,s2,...sn中选取字符串 |
13.3.1 计算字符串字符数的函数和字符长度的函数
CHAR_LENGTH(s)函数计算字符串s的字符数,LENGTH(s)函数计算字符串s的长度。
【示例13-13】下面将演示CHAR_LENGTH(s)函数和LENGTH(s)函数的使用。
mysql>SELECT s ,CHAR_LENGTH(s),length(s) FROM t2; +-------+----------------+-----------+ | s | CHAR_LENGTH(s) | length(s) | +-------+----------------+-----------+ |beijing| 7 | 7 | +-------+----------------+-----------+ 1 row in set(0.00 sec)
结果显示，12表中s字段的值为“beijing”。该字符串包含7个字符，长度为7。
说明：字符串“beijing”共有7个字符，但是占用的空间是8个字节。因为，每个字符串都是以\0结束的，\0占用一个字节的空间。LENGTH(s)函数计算字符串s的长度，这个长度是指字符数，而不是指占用的空间。因此字符串“beijing”的长度 为7。
13.3.2 合并字符串的函数CONCAT(s1 ,s2,...)和C0NCAT_WS(x,s1 ,s2,...)
CONCAT(s1 ,s2,...)函数和C0NCAT_WS(x,s1 ,s2,...)函数都可以将sl、s2等多个字符串合并成一个字符串。但〔C0NCAT_WS(x,s1 ,s2,...)可以将各字符串直接用参数x隔开。
【示例13-14】下面将演示CONCAT(s1 ,s2,...)和C0NCAT_WS(x,s1 ,s2,...)函数的使用。
mysql> SELECT CONCAT('bei','ji','ng'),CONCAT('-','bei','ji','ng');
+-------------------------+-----------------------------+
| CONCAT('bei','ji','ng') | CONCAT('-','bei','ji','ng') |
+-------------------------+-----------------------------+
| beijing | -beijing |
+-------------------------+-----------------------------+
1 row in set (0.00 sec)
结果显示，两个函数将3个字符串合并成一个字符串。但是后者使用“-”将每个字符串隔开。
技巧：CONCAT(s1 ,s2,...)和C0NCAT_WS(x,s1 ,s2,...)函数都是用来合并字符串，这两个函数在操作字符串时非常有用。例如，为了让空格显示更加明显，可以在空格两边加上“+”。这可以使用CONCAT('+',' ’，+)的方式组合起来，显示出来 就会是“+ +”，这样空格看起来更加明显。
13.3.3 替换字符串的函数 INSERT(s1,x,len,s2)
INSERT(s1,x,len,s2)函数将字符串sl中x位置开始长度为len的字符串用s2替换。
【示例13-15】下面将演示取INSERT(s1,x,len,s2)函数的使用。
mysql> SELECT s, INSERT(s,4,4,'fang') FROM t2; +---------+---------------------+ | s | INSERT(s,4,4,'fang')| +---------+---------------------+ | beijing | beifang | +---------+---------------------+ 1 row in set(0.00 sec)
结果显示，s字段的值为字符"beijing"。INSERT(s,4,4,'fang')是将s中的第4个字符 开始的4个字符用"fang"替换。也就是"jing"被"fang"替换。所以替换后的结果是"beifang"。
13.3.4字母大小写转换函数
UPPER(s)函数和UPCASE(s)函数将字符串s的所有字母变成大写字母；LOWER(s) 和LCASE(s)函数将字符串s的所有字母变成小写字母。
【示例 13-16】下面将演示 UPPER(s)、UCASE(s)、LOWER(s)和LCASE(s)4 个函数的使用。
mysql> SELECT UPPER('mysqr'),UCASE('mysql'),LOWER('MYSQL'),LCASE('MYSQL');
+----------------+----------------+----------------+----------------+
| UPPER('mysqr') | UCASE('mysql') | LOWER('MYSQL') | LCASE('MYSQL') |
+----------------+----------------+----------------+----------------+
| MYSQR | MYSQL | mysql | mysql |
+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)
结果显示，UPPER('mysqr)和 UCASE(mysql')将 “ mysql ” 变成 “ MYSQL ” ；
LOWER('MYSQL')PLCASE('MYSQL')将“MYSQL” 变成 “mysql”。
13.3.5获取指定长度的字符串的函数LEFT(s,n),RIGHT(s,n)
LEFT(s,n)函数返回字符串s的前n个字符;RIGHT(s,n)函数返回字符串s的后n个字符。
【示例13-17】下面将演示LEFT(s,n),RIGHT(s,n)函数的使用。
mysql> SELECT s, LEFT(s,3), RIGHT(s,4) FROM t2; +-------+-----------+-----------+ | s | LEFT(s,3) | RIGHT(s,4)| +-------+-----------+-----------+ |beijing| bei | jing | +-------+-----------+-----------+ 1 row in set(0.00 sec)
结果显示，s字段的值为字符串"beijing";LEFT(s,3)返回前3个字母bei”; RIGHT(s,4)返回后4个字母“jing”。
13.3.6 填充字符串的函数LPAD(s1,len,s2),RPAD(s1,len,s2)
LPAD(s1,len,s2)函数将字符串s2填充到sl的开始处，使字符串长度达到len; RPAD(s1,len,s2)函数将字符串s2填充到sl的结尾处，使字符串长度达到len。
【示例13-18】下面将演示LPAD(s1,len,s2),RPAD(s1,len,s2)函数的使用。
mysql> SELECT s, LPAD(s,10,'+-'), RPAD(s,10,'+-') FROM t2; +--------+------------------+-----------------+ | s | LPAD(s,10,'+-') | RPAD(s,10,'+-') | +--------+------------------+-----------------+ |beijing | +-+beijing | beijinng+-+ | +--------+------------------+-----------------+ 1 row in set(0.00 sec)
结果显示，LPAD(s,10,'+-')将“+-”填充到字符串“beijing”的最前面，使字符串长度变为10。填充后字符串变为“+-+beijing”。RPAD(s，10，’+-'^)将“+-”填充到字符串“beijing”的最后，使字符串长度变为10。填充后字符串变为“beijing+-+”。
13.3.7删除空格的函数LTRIM(s)、RTRlM(s)和TRIM(s)/
LTRIM(s)函数将去掉字符串s开始处的空格；RTRIM(s)函数将去掉字符串s结尾处的空格;TRIM(s)函数将去掉字符串s开始处和结尾处的空格。
【示例13-19】下面将演示LTRIM(s)、RTRIM(s)、TWM(s)3个函数的使用。使用的字符串是‘ me ’。该字符串的开头和结尾各有一个空格。因为空格不好显示，所以使用CONCAT()函数来将字符串与‘+’连接起来。
mysql> SELECT CONCAT('+','me','+'), CONCAT('+',LTRIM('me'),'+');
+----------------------+-----------------------------+
| CONCAT('+','me','+') | CONCAT('+',LTRIM('me'),'+') |
+----------------------+-----------------------------+
| +me+ | +me+ |
+----------------------+-----------------------------+
1 row in set (0.00 sec)
+-----------------------------+----------------------------+
| CONCAT('+',RTRIM('me'),'+') | CONCAT('+',TRIM('me'),'+') |
+-----------------------------+----------------------------+
| +me+ | +me+ |
+-----------------------------+----------------------------+
1 row in set (0.00 sec)
结果显示，直接使用CONCAT('+','me','+')时，系统会保留“me”前面和后面的空格; LTRIM(' me ’)将字符串前端的空格去掉了； RTRIM(' me ')将字符串最后的空格去掉了； TRIM(' me ’)将字符串最前端和最后面的空格去掉。
13.3.8删除指定字符串的函数TRIM(s1 FROM s)
TRIM(sl FROM s)函数将去掉字符串s中开始处和结尾处的字符串sl。
【示例13-20】下面将演示TRIM(sl FROM 8)函数的使用。
mysql> SELECT TRIM('ab' FROM 'ababddddabddab');
+----------------------------------+
| TRIM('ab' FROM 'ababddddabddab') |
+----------------------------------+
| ddddabdd |
+----------------------------------+
1 row in set (0.00 sec)
结果显示，字符串“ababddddabddab”最前端和最后面的字符串“ab”去掉。而中间的“ab”还保留着。
技巧：通常，数据库中数据最好不要以空格开头或结尾。除非有特殊需要，例如，存储一篇文章时，需要使用空格调整格式。在输入字符串数据时，最好使用TRIM(s)去掉字符串开始和结束部分的空格。如果需要过滤掉某些敏感字符，可以使用 TRIM(sl FROM 8)函数将指定字符过滤掉。
13.3.9重复生成字符串的函数REPEAT(s,n)
REPEAT(s,n)函数将字符串s重复n次。
【示例13-21】下面将演示REPEAT(s,n)函数的使用。
mysql> SELECT REPEAT('mysql-',2);
+--------------------+
| REPEAT('mysql-',2) |
+--------------------+
| mysql-mysql- |
+--------------------+
1 row in set (0.00 sec)
结果显示，REPEAT(mysql-',2)返回的结果为mysql-mysql-。
13.3.10空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)
SPACE(n)函数返回n个空格；REPLACE(s,s1,s2)函数将字符串s2替代字符串s中的字符串sl。
【示例13-22】下面将演示SPACE(n),REPLACE(s,s1,s2)两个函数的使用。
mysql> SELECT CONCAT('+',SPACE(4),'+'),REPLACE('mysql','sql','book');
+--------------------------+-------------------------------+
| CONCAT('+',SPACE(4),'+') | REPLACE('mysql','sql','book') |
+--------------------------+-------------------------------+
| + + | mybook |
+--------------------------+-------------------------------+
1 row in set (0.00 sec)
结果显示，SPACE(4)返回了4个空格；REPLACE('mysqr,'sql','book')"将"mysql"中的 “sql” 用 “book” 替换。
13.3.11 比较字符串大小的函数STRCMP(s1，s2)
STRCMP(s1，s2)函数用来比较字符串sl和s2。如果sl大于s2,等于$2,结果返回0;如果s1小于s2，结果返回-1。
【示例13-23】下面将演示STRCMP(sl,s2)函数的使用。
mysql> SELECT STRCMP('abc','abb'),STRCMP('abc','abc'),STRCMP('abc','abd');
+---------------------+---------------------+---------------------+
| STRCMP('abc','abb') | STRCMP('abc','abc') | STRCMP('abc','abd') |
+---------------------+---------------------+---------------------+
| 1 | 0 | -1 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
因为abc大于abb所以结果返回1；因为abc等于abc,所以结果返回0;因为abc小于abd所以结果返回-1。
13.3.12 获取子串的函数SUBSTRING(s,nJenpn MID(s,n,len)
SUBSTRING(s,nJenpn MID(s,n,len)从字符串s的第n个位置开始获取长度为len的字符串。
mysql>SELECT s,SUBSTRING(s,4,3),MID(s,4,3) FROM t2; +-------+------------------+--------------+ | s | SUBSTRING(s,4,3) | MID(s,4,3) | +-------+------------------+--------------+ |beijing| jin | jin | +-------+------------------+--------------+ 1 row in set (0.00 sec)
结果显示，两个函数从第4个位置开始，获取长度为3的字符串。
13.3.13匹配子串开始位置的函数
LOCATE(s 1 ,s),POSITION(s 1 W 8)和 INSfSTR(s,sl)3个函数从字符串s中获取sl的开始位置。
【示例 13-25】下面将演示 LOCATE(sl,s)、POSITION(sl EN 5)和 DsfSTR(s,sl)3 个函数的使用。
mysql> SELECT s ,LOCATE('jin',s), POSITION('jin. IN s), INSTR(s,'jin') FROM t2;
+-------+------------------+------------------------+-----------------+
| s | LOCATE('jin',s) | POSITION('jin' IN s) | INSTR(s,'jin') |
+-------+------------------+------------------------+-----------------+
|beijing| 4 | 4 | 4 |
+-------+------------------+------------------------+-----------------+
1 row in set (0.00 sec)
结果显示，s字段的值中“jin”的起始位置为4。
13.3.14字符串逆序的函数REVERSE(s)
REVERSE(s)函数将字符串s的顺序反过来。
【示例13-26】下面将演示REVERSE(s)函数的使用。
mysqt> SELECT s, REVERSE(s) FROM t2; +---------+-------------+ | s | REVERSE(s) | +---------+-------------+ | beijing | gnijieb | +---------+-------------+ 1 row in set (0.00 sec)
结果显示，REVERSE(s)将字符串s的顺序都反过来。
13.3.15返回指定位置的字符串的函数
ELT(n,sl,s2,...)函数返回第n个字符串。
【示例13-27】下面将演示ELT(n,sl,s2,...)函数的使用。
+-----------------------------+ | ELT(2,'me','my','he','she') | +-----------------------------+ | my | +-----------------------------+ 1 row in set (0.00 sec)
结果显示，('me','my','he','she')的第二个字符串是“my”。
13.3.16返回指定字符串位置的函数FELD(s,s1,s2,...)
FELD(s,s1,s2,...)函数返回第一个与字符串S匹配的字符串的位置。
【示例13-28】下面将演示FELD(s,s1,s2,...)函数的使用。
mysql> SELECT FIELD('he','me','my','he','she');
+----------------------------------+
| FIELD('he','me','my','he','she') |
+----------------------------------+
| 3 |
+----------------------------------+
1 row in set (0.00 sec)
结果显示，“he”是('me','my','he','she')中的第3个字符串。
13.3.17返回子串位置的函数FIND_IN_SET(S1,S2)
FIND_IN_SET(S1,S2)函数返回在字符串s2中与sl匹配的字符串的位置。其中，字符串s2中包含了若干个用逗号隔开的字符串。
【示例13-29】下面将演示FIND_IN_SET(S1,S2)函数的使用。
mysql> SELECT FIND_IN_SET('like','i,like,bei,jing');
+---------------------------------------+
| FIND_IN_SET('like','i,like,bei,jing') |
+---------------------------------------+
| 2 |
+---------------------------------------+
1 row in set (0.00 sec)
结果显示，字符串“like”在字符串“i，like，bei，jing”的第二个位置上。
13.3.18 选取字符串的函数 MAKE_SET(x,s1,s2,...)
MAKE_SET(x,s1,s2,...)函数按x的二进制数从sl,s2，...，sn中选取字符串。例如12的二进制是1100。这个二进制数从右到左的第3位和第4位是1,所以选取s3和s4。
【示例13-30】下面将演示MAKE_SET(x,sl，s2，…)函数的使用。
mysql> SELECT MAKE_SET(11 ,'a','b','c','d'), MAKE_SET(7,'a','b',' +-------------------------------+-----------------------------+ | MAKE_SET(11 ,'a','b','c','d') | MAKE_SET(7,'a','b','c','d') | +-------------------------------+-----------------------------+ | a,b,d | a,b,c | +-------------------------------+-----------------------------+ 1 row in set (0.00 sec)
因为11的二进制数是1011,从右到左的第1位、第2位和第4位是1,所以结果选取 a、匕和山因为7的二进制数是111,从右到左的第1位、第2位和第3位是1,所以结果 选取a、b和c。 含注意：使用MAKE_SET(x,sl,s2,...)函数时一定要特别注意。该函数是按照x所指定的顺 序查找指定字符串。数据库系统会先将义转换为二进制数，然后选取位数为1的 位置对应的字符串。读取二进制数的顺序是从右到左的，最右边的是第一位。
