下载掌阅APP，畅读海量书库
立即打开
字符串函数主要用来处理数据库中的字符串数据。MySQL中的字符串函数有字符串长度计算函数、字符串合并函数、字符串替换函数、字符串比较函数、查找指定字符串位置函数等。本节将介绍各种字符串函数的作用和用法。
CHAR_LENGTH(str)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。
【例4.26】使用CHAR_LENGTH函数计算字符串字符个数，输入语句如下：
mysql> SELECT CHAR_LENGTH('date'), CHAR_LENGTH('egg');
+-----------------------------+---------------------+
| CHAR_LENGTH('date') | CHAR_LENGTH('egg') |
+-----------------------------+---------------------+
| 4 | 3 |
+-----------------------------+---------------------+
LENGTH(str)返回值为字符串的字节长度，使用utf8（UNICODE的一种变长字符编码，又称万国码）编码字符集时，一个汉字是3字节，一个数字或字母为1字节。
【例4.27】使用LENGTH函数计算字符串长度，输入语句如下：
mysql> SELECT LENGTH('date'), LENGTH('egg');
+----------------+---------------+
| LENGTH('date') | LENGTH('egg') |
+----------------+---------------+
| 4 | 3 |
+----------------+---------------+
可以看到，计算的结果与CHAR_LENGTH相同，因为英文字符的个数和所占的字节相同，一个字符占1字节。
CONCAT(s1,s2,…)返回结果为连接参数产生的字符串，或许有一个或多个参数。如有任何一个参数为NULL，则返回值为NULL；如果所有参数均为非二进制字符串，则结果为非二进制字符串；如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。
【例4.28】使用CONCAT函数连接字符串，输入语句如下：
mysql> SELECT CONCAT('My SQL', '8.0'),CONCAT('My',NULL, 'SQL');
+-------------------------+--------------------------+
| CONCAT('My SQL', '8.0') | CONCAT('My',NULL, 'SQL') |
+-------------------------+--------------------------+
| My SQL8.0 | NULL |
+-------------------------+--------------------------+
CONCAT('My SQL', '8.0')返回两个字符串连接后的字符串；CONCAT('My',NULL, 'SQL')中有一个参数为NULL，因此返回结果为NULL。
在CONCAT_WS(x,s1,s2,…)中，CONCAT_WS代表CONCAT With Separator，是CONCAT()的特殊形式。第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其他参数。如果分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值。
【例4.29】使用CONCAT_WS函数连接带分隔符的字符串，输入语句如下：
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 |
+-----------------------------------+-----------------------------------+
CONCAT_WS('-', '1st','2nd', '3rd')使用分隔符“-”将3个字符串连接成一个字符串，结果为“1st-2nd-3rd”；CONCAT_WS('*', '1st', NULL, '3rd')使用分隔符“*”将两个字符串连接成一个字符串，同时忽略NULL值。
INSERT(s1,x,len,s2)返回字符串s1，其子字符串起始于x位置和被字符串s2取代的len字符。如果x超过字符串长度，则返回值为原始字符串。如果len的长度大于其他字符串的长度，则从位置x开始替换。若任何一个参数为NULL，则返回值为NULL。
【例4.30】使用INSERT函数进行字符串替代操作，输入语句如下：
MySQL> SELECT INSERT('Quest', 2, 4, 'What') AS col1, INSERT('Quest', -1, 4, 'What') AS col2,
INSERT('Quest', 3, 100, 'Wh') AS col3;
+--------+-------+-----------+
| col1 | col2 | col3 |
+--------+-------+-----------+
| QWhat | Quest | QuWhat |
+--------+-------+-----------+
第一个函数INSERT('Quest', 2, 4, 'What')将“Quest”第2个字符开始、长度为4的字符串替换为What，结果为“QWhat”；第二个函数INSERT('Quest', -1, 4, 'What')中起始位置-1超出了字符串长度，直接返回原字符；第三个函数INSERT('Quest', 3, 100, 'What')替换长度超出了原字符串长度，则从第3个字符开始，截取后面所有的字符，并替换为指定字符What，结果为“QuWhat”。
LOWER (str)或者LCASE (str)可以将字符串str中的字母字符全部转换成小写字母。
【例4.31】使用LOWER函数或者LCASE函数，将字符串中所有字母字符转换为小写，输入语句如下：
mysql> SELECT LOWER('BEAUTIFUL'), LCASE('Well');
+--------------------+---------------+
| LOWER('BEAUTIFUL') | LCASE('Well') |
+--------------------+---------------+
| beautiful | well |
+--------------------+---------------+
由结果可以看到，原来所有字母为大写的，全部转换为小写，如“BEAUTIFUL”，转换之后为“beautiful”；大小写字母混合的字符串，小写不变，大写字母转换为小写字母，如“WelL”，转换之后为“well”。
UPPER(str)或者UCASE(str)可以将字符串str中的字母字符全部转换成大写字母。
【例4.32】使用UPPER函数或者UCASE函数，将字符串中所有字母字符转换为大写，输入语句如下：
mysql> SELECT UPPER('black'), UCASE('BLacK');
+----------------+----------------+
| UPPER('black') | UCASE('BLacK') |
+----------------+----------------+
| BLACK | BLACK |
+----------------+----------------+
由结果可以看到，原来所有字母字符为小写的，全部转换为大写，如“black”，转换之后为“BLACK”；大小写字母混合的字符串，大写不变，小写字母转换为大写字母，如“BLacK”，转换之后为“BLACK”。
LEFT(s,n)返回字符串s开始的最左边n个字符。
【例4.33】使用LEFT函数返回字符串中左边的字符，输入语句如下：
mysql> SELECT LEFT('football', 5);
+---------------------+
| LEFT('football', 5) |
+---------------------+
| footb |
+---------------------+
函数返回字符串“football”左边开始的、长度为5的子字符串，结果为“footb”。RIGHT(s,n)返回字符串str最右边的n个字符。
【例4.34】使用RIGHT函数返回字符串中右边的字符，输入语句如下：
MySQL> SELECT RIGHT('football', 4);
+----------------------+
| RIGHT('football', 4) |
+----------------------+
| ball |
+----------------------+
函数返回字符串“football”右边开始的、长度为4的子字符串，结果为“ball”。
LPAD(s1,len,s2)返回字符串s1，其左边由字符串s2填补到len字符长度。假如s1的长度大于len，则返回值被缩短至len字符。
【例4.35】使用LPAD函数对字符串进行填充操作，输入语句如下：
MySQL> SELECT LPAD('hello',4,'??'), LPAD('hello',10,'??');
+----------------------+-----------------------+
| LPAD('hello',4,'??') | LPAD('hello',10,'??') |
+----------------------+-----------------------+
| hell | ?????hello |
+----------------------+-----------------------+
字符串“hello”长度大于4，不需要填充，因此LPAD(’hello’,4,’??’)只返回被缩短的、长度为4的子串“hell”；字符串“hello”长度小于10，LPAD(’hello’,10,’??’)返回结果为“?????hello”，左侧填充“？”，长度为10。
RPAD(s1,len,s2)返回字符串sl，其右边被字符串s2填补至len字符长度。假如字符串s1的长度大于len，则返回值被缩短到len字符长度。
【例4.36】使用RPAD函数对字符串进行填充操作，输入语句如下：
mysql> SELECT RPAD('hello',4,'?'), RPAD('hello',10,'?');
+---------------------+----------------------+
| RPAD('hello',4,'?') | RPAD('hello',10,'?') |
+---------------------+----------------------+
| hell | hello????? |
+---------------------+----------------------+
字符串“hello”长度大于4，不需要填充，因此RPAD('hello',4,'?')只返回被缩短的、长度为4的子串“hell”；字符串“hello”长度小于10， RPAD('hello',10,'?')返回结果为“hello?????”，右侧填充“？”，长度为10。
LTRIM(s)返回字符串s，字符串左侧空格字符被删除。
【例4.37】使用LTRIM函数删除字符串左边的空格，输入语句如下：
mysql> SELECT '( book )',CONCAT('(',LTRIM(' book '),')');
+-------------+-----------------------------------+
| ( book ) | CONCAT('(',LTRIM(' book '),')') |
+-------------+-----------------------------------+
| ( book ) | (book ) |
+-------------+-----------------------------------+
LTRIM只删除字符串左边的空格，而右边的空格不会被删除，“ book ”删除左边空格之后的结果为“book ”。
RTRIM(s)返回字符串s，字符串右侧空格字符被删除。
【例4.38】使用RTRIM函数删除字符串右边的空格，输入语句如下：
mysql> SELECT '( book )',CONCAT('(', RTRIM (' book '),')');
+------------+-------------------------------------+
| ( book ) | CONCAT('(', RTRIM (' book '),')') |
+------------+-------------------------------------+
| ( book ) | ( book) |
+------------+-------------------------------------+
RTRIM只删除字符串右边的空格，左边的空格不会被删除，“ book ”删除右边空格之后的结果为“ book”。
TRIM(s)删除字符串s左右两侧的空格。
【例4.39】使用TRIM函数删除字符串两侧的空格，使用语句如下：
mysql> SELECT '( book )',CONCAT('(', TRIM(' book '),')');
+------------+-----------------------------------+
| ( book ) | CONCAT('(', TRIM(' book '),')') |
+------------+-----------------------------------+
| ( book ) | (book) |
+------------+-----------------------------------+
可以看到，函数执行之后字符串“ book ”两边的空格都被删除，结果为“book”。
TRIM(s1 FROM s)删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定的情况下，会删除空格。
【例4.40】使用TRIM(s1 FROM s)函数删除字符串两端指定的字符，输入语句如下：
mysql> SELECT TRIM('xy' FROM 'xyxboxyokxxyxy') ;
+----------------------------------+
| TRIM('xy' FROM 'xyxboxyokxxyxy') |
+----------------------------------+
| xboxyokx |
+----------------------------------+
删除字符串“xyxboxyokxxyxy”两端的重复字符串“xy”，而中间的“xy”并不删除，结果为“xboxyokx”。
REPEAT(s,n)返回一个由重复的字符串s组成的字符串，字符串s的数目等于n。若n<=0，则返回一个空字符串。若s或n为NULL，则返回NULL。
【例4.41】使用REPEAT函数重复生成相同的字符串，输入语句如下：
mysql> SELECT REPEAT('mysql', 3);
+--------------------+
| REPEAT('mysql', 3) |
+--------------------+
| mysqlmysqlmysql |
+--------------------+
REPEAT('MySQL', 3)函数返回的字符串由3个重复的“mysql”字符串组成。
SPACE(n)返回一个由n个空格组成的字符串。
【例4.42】使用SPACE函数生成由空格组成的字符串，输入语句如下：
mysql> SELECT CONCAT('(', SPACE(6), ')' );
+-----------------------------+
| CONCAT('(', SPACE(6), ')' ) |
+-----------------------------+
| ( ) |
+-----------------------------+
SPACE(6)返回的字符串由6个空格组成。
REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。
【例4.43】使用REPLACE函数进行字符串替代操作，输入语句如下：
mysql> SELECT REPLACE('xxx.mysql.com', 'x', 'w');
+------------------------------------+
| REPLACE('xxx.mysql.com', 'x', 'w') |
+------------------------------------+
| www.mysql.com |
+------------------------------------+
REPLACE('xxx.MySQL.com', 'x', 'w')将“xxx.mysql.com”字符串中的“x”字符替换为“w”字符，结果为“www.mysql.com”。
STRCMP(s1,s2)比较字符串s1与s2的大小。若s1与s2所有的字符均相同，则返回0；若根据当前分类次序，第一个参数小于第二个，则返回－1；其他情况返回1。
【例4.44】使用STRCMP函数比较字符串大小，输入语句如下：
mysql> SELECT STRCMP('txt', 'txt2'),STRCMP('txt2', 'txt'), STRCMP('txt', 'txt');
+-----------------------+-----------------------+----------------------+
| STRCMP('txt', 'txt2') | STRCMP('txt2', 'txt') | STRCMP('txt', 'txt') |
+-----------------------+-----------------------+----------------------+
| -1 | 1 | 0 |
+-----------------------+-----------------------+----------------------+
“txt”小于“txt2”，因此STRCMP('txt', 'txt2')返回结果为－1，STRCMP('txt2', 'txt')返回结果为1；“txt”与“txt”相等，因此STRCMP('txt', 'txt')返回结果为0。
SUBSTRING(s,n,len)带有len参数的格式，从字符串s返回一个长度与len字符相同的子字符串，起始于位置n。也可能对n使用一个负值，如果是这种情况，则子字符串的位置起始于字符串结尾的n字符，即倒数第n个字符，而不是字符串的开头位置。
【例4.45】使用SUBSTRING函数获取指定位置处的子字符串，输入语句如下：
MySQL> SELECT SUBSTRING('breakfast',5) AS col1, SUBSTRING('breakfast',5,3) AS
col2,SUBSTRING('lunch', -3) AS col3,SUBSTRING('lunch', -5, 3) AS col4;
+-------+-------+------+-------+
| col1 | col2 | col3 | col4 |
+-------+-------+------+-------+
| kfast | kfa | nch | lun |
+-------+-------+------+-------+
SUBSTRING('breakfast',5)返回从第5个位置开始到字符串结尾的子字符串，结果为“kfast”；SUBSTRING('breakfast',5,3)返回从第5个位置开始长度为3的子字符串，结果为“kfa”；SUBSTRING('lunch', -3)返回从结尾开始第3个位置到字符串结尾的子字符串，结果为“nch”；SUBSTRING('lunch', -5, 3)返回从结尾开始第5个位置，即字符串开头起、长度为3的子字符串，结果为“lun”。
MID(s,n,len)函数与SUBSTRING(s,n,len)的作用相同。
【例4.46】使用MID函数获取指定位置处的子字符串，输入语句如下：
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 |
+-------+-------+------+------+
可以看到MID和SUBSTRING的处理结果是一样的。
提示： 如果对len使用的是一个小于1的值，则结果始终为空字符串。
LOCATE(str1,str)、POSITION(str1 IN str)和INSTR(str, str1)3个函数的作用相同，返回子字符串str1在字符串str中的开始位置。
【例4.47】使用LOCATE、POSITION、INSTR函数，查找字符串中指定子字符串的开始位置，输入语句如下：
mysql> SELECT LOCATE('ball','football'),POSITION('ball'IN 'football'),INSTR ('football', 'ball');
+---------------------------+-------------------------------+----------------------------+
| LOCATE('ball','football') | POSITION('ball'IN 'football') | INSTR ('football', 'ball') |
+---------------------------+-------------------------------+----------------------------+
| 5 | 5 | 5 |
+---------------------------+-------------------------------+----------------------------+
子字符串“ball”在字符串“football”中从第5个字母位置开始，因此3个函数返回结果都为5。
REVERSE(s)函数将字符串s反转，返回的字符串的顺序和s字符串顺序相反。
【例4.48】使用REVERSE函数反转字符串，输入语句如下：
mysql> SELECT REVERSE('abc');
+--------------------+
| REVERSE('abc') |
+--------------------+
| cba |
+--------------------+
可以看到，字符串“abc”经过REVERSE函数处理之后，所有字符顺序被反转，结果为“cba”。
对于ELT(N,字符串1,字符串2,字符串3,...,字符串N)函数，若N=1，则返回值为字符串1；若N=2，则返回值为字符串2；以此类推；若N小于1或大于参数的个数，则返回值为NULL。
【例4.49】使用ELT函数返回指定位置字符串，输入语句如下：
mysql> SELECT ELT(3,'1st','2nd','3rd'), ELT(3,'net','os');
+--------------------------+-------------------+
| ELT(3,'1st','2nd','3rd') | ELT(3,'net','os') |
+--------------------------+-------------------+
| 3rd | NULL |
+--------------------------+-------------------+
由结果可以看到，ELT(3,'1st','2nd','3rd')返回第3个位置的字符串“3rd”；ELT(3,'net','os')指定返回字符串位置超出参数个数，返回NULL。
FIELD(s,s1,s2,…,sn)函数返回字符串s在列表s1,s2,…,sn中第一次出现的位置，在找不到s的情况下，返回值为0。如果s为NULL，则返回值为0，原因是NULL不能与任何值进行同等比较。
【例4.50】使用FIELD函数返回指定字符串第一次出现的位置，输入语句如下：
mysql> SELECT FIELD('Hi', 'hihi', 'Hey', 'Hi', 'bas') as col1, FIELD('Hi', 'Hey', 'Lo', 'Hilo',
'foo') as col2;
+------+-------+
| col1 | col2 |
+------+-------+
| 3 | 0 |
+------+-------+
在FIELD('Hi', 'hihi', 'Hey', 'Hi', 'bas')函数中，字符串“Hi”出现在列表的第3个字符串位置，因此返回结果为3；FIELD('Hi', 'Hey', 'Lo', 'Hilo', 'foo')列表中没有字符串“Hi”，因此返回结果为0。
FIND_IN_SET(s1,s2)函数返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号“,”分开的字符串组成的列表。如果s1不在s2中或s2为空字符串，则返回值为0。如果任意一个参数为NULL，则返回值为NULL。这个函数在第一个参数包含一个逗号“,”的情况下，将无法正常运行。
【例4.51】使用FIND_IN_SET()函数返回子字符串在字符串列表中的位置，输入语句如下：
mysql> SELECT FIND_IN_SET('Hi','hihi,Hey,Hi,bas');
+---------------------------------------------+
| FIND_IN_SET('Hi','hihi,Hey,Hi,bas') |
+---------------------------------------------+
| 3 |
+---------------------------------------------+
虽然FIND_IN_SET()和FIELD()两个函数格式不同，但作用类似，都可以返回指定字符串在字符串列表中的位置。
MAKE_SET(x,s1,s2,…,sn)函数按x的二进制数从s1,s2,…,sn中选取字符串。例如，5的二进制是0101，这个二进制从右往左的第1位和第3位是1，所以选取s1和s3。s1,s2,...,sn中的NULL值不会被添加到结果中。
【例4.52】使用MAKE_SET函数根据二进制位选取指定字符串，输入语句如下：
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的二进制值为0001，4的二进制值为0100，1与4进行或操作之后的二进制值为0101，从右到左第1位和第3位为1。MAKE_SET(1,'a','b','c')返回第1个字符串；SET(1 | 4,'hello','nice','world')返回从左端开始第1个和第3个字符串组成的字符串；NULL不会添加到结果中，因此SET(1 |4,'hello','nice',NULL,'world')只返回第1个字符串“hello”；SET(0,'a','b','c')返回空字符串。