购买
下载掌阅APP，畅读海量书库
立即打开
畅读海量书库
扫码下载掌阅APP

4.3　字符串函数

字符串函数主要用来处理数据库中的字符串数据。MySQL中的字符串函数有计算字符串长度函数、字符串合并函数、字符串替换函数、字符串比较函数、查找指定字符串位置函数等。本节将介绍各种字符串函数的功能和用法。

4.3.1　计算字符串的字符数的函数和计算字符串长度的函数

CHAR_LENGTH(str)的返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。

【 例4.26 】使用CHAR_LENGTH()函数计算字符串中的字符个数，SQL语句如下： 

    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()函数计算字符串长度，SQL语句如下： 

    mysql> SELECT LENGTH('date'), LENGTH('egg');
    +----------------+---------------+
    | LENGTH('date') | LENGTH('egg') |
    +----------------+---------------+
    |           4    |          3    |
    +----------------+---------------+

可以看到，LENGTH()函数的计算结果与CHAR_LENGTH()相同，因为英文字符的个数和所占的字节数相同，一个字符占1字节。

4.3.2　合并字符串函数CONCAT(s1,s2,...)、CONCAT_WS(x,s1,s2,...)

CONCAT(s1,s2,...)的返回结果为所有参数相连而产生的新字符串，或许有一个或多个参数。如果任何一个参数为NULL，则返回值为NULL；如果所有参数均为非二进制字符串，则结果为非二进制字符串；如果参数中含有二进制字符串，则结果为一个二进制字符串。

【 例4.28 】使用CONCAT()函数连接字符串，SQL语句如下： 

    mysql> SELECT CONCAT('My SQL', '9.0'),CONCAT('My',NULL, 'SQL');
    +-------------------------+--------------------------+
    | CONCAT('My SQL', '9.0') | CONCAT('My',NULL, 'SQL') |
    +-------------------------+--------------------------+
    | My SQL9.0               | NULL                     |
    +-------------------------+--------------------------+

CONCAT('My SQL', '9.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()函数连接带分隔符的字符串，SQL语句如下： 

    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。

4.3.3　替换字符串的函数INSERT(s1,x,len,s2)

NSERT(s1,x,len,s2)返回一个新的字符串，其内容是将字符串s1中从位置x开始的len个字符替换为字符串s2的内容。如果x超过字符串s1的长度，则返回值为原始字符串；如果len的长度超出了字符串s1的长度，则从位置x开始替换到s1的末尾；若任何一个参数为NULL，则返回值为NULL。

【 例4.30 】使用INSERT()函数进行字符串替换操作，SQL语句如下： 

     MySQL> SELECT INSERT('Quest', 2, 4, 'What') AS col1, INSERT('Quest', -1, 4, 'What')
AS col2, INSERT('Quest', 3, 100, 'What') 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”。

4.3.4　字母大小写转换函数

LOWER (str)或者LCASE (str)可以将字符串str中的大写字母全部转换为小写字母。

【 例4.31 】使用LOWER()函数或者LCASE()函数将字符串中所有大写字母转换为小写字母，SQL语句如下： 

    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()函数将字符串中所有小写字母转换为大写字母，SQL语句如下： 

    mysql> SELECT UPPER('black'), UCASE('BLacK');
    +----------------+----------------+
    | UPPER('black') | UCASE('BLacK') |
    +----------------+----------------+
    | BLACK          | BLACK          |
    +----------------+----------------+

由结果可以看到，原来所有字母为小写的，全部转换为大写，如“black”转换之后为“BLACK”；大小写字母混合的字符串，大写不变，小写字母转换为大写字母，如“BLacK”转换之后为“BLACK”。

4.3.5　获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)

LEFT(s,n)返回字符串s中的最左边n个字符。

【 例4.33 】使用LEFT()函数返回字符串中左边的字符，SQL语句如下： 

    mysql> SELECT LEFT('football', 5);
    +---------------------+
    | LEFT('football', 5) |
    +---------------------+
    | footb               |
    +---------------------+

LEFT()函数返回字符串“football”从左边开始的长度为5的子字符串，结果为“footb”。

RIGHT(s,n)返回字符串str中的最右边的n个字符。

【 例4.34 】使用RIGHT()函数返回字符串中右边的字符，SQL语句如下： 

    MySQL> SELECT RIGHT('football', 4);
    +----------------------+
    | RIGHT('football', 4) |
    +----------------------+
    | ball                 |
    +----------------------+

RIGHT()函数返回字符串“football”从右边开始的长度为4的子字符串，结果为“ball”。

4.3.6　填充字符串的函数LPAD(s1,len,s2)和RPAD(s1,len,s2)

LPAD(s1,len,s2)返回一个新字符串，新字符串的内容是将字符串s1的左侧用字符串s2填充至len个字符长度。如果s1的长度本身已经大于或等于len，则返回值被缩短至len个字符。

【 例4.35 】使用LPAD()函数对字符串进行填充操作，SQL语句如下： 

    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)返回一个新字符串，新字符串的内容是将字符串s1的右侧用字符串s2填充至len个字符长度。如果s1的长度本身已经大于或等于len，则返回值被缩短至len个字符。

【 例4.36 】使用RPAD()函数对字符串进行填充操作，SQL语句如下： 

    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。

4.3.7　删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)

LTRIM(s)返回被删除了左侧空格的字符串s。

【 例4.37 】使用LTRIM()函数删除字符串左边的空格，SQL语句如下： 

    mysql> SELECT '(  book  )',CONCAT('(',LTRIM('  book  '),')');
    +------------+-----------------------------------+
    | (  book  ) | CONCAT('(',LTRIM('  book  '),')') |
    +------------+-----------------------------------+
    | (  book  ) | (book  )                          |
    +------------+-----------------------------------+

LTRIM()只删除字符串左边的空格，而右边的空格不会被删除，因此“ book ”在删除左边空格之后的结果为“book ”。

RTRIM(s)返回被删除了右侧空格的字符串s。

【 例4.38 】使用RTRIM()函数删除字符串右边的空格，SQL语句如下： 

    mysql> SELECT '(  book  )',CONCAT('(', RTRIM ('  book  '),')');
    +------------+-------------------------------------+
    | (  book  ) | CONCAT('(', RTRIM ('  book  '),')') |
    +------------+-------------------------------------+
    | (  book  ) | (  book)                            |
    +------------+-------------------------------------+

RTRIM()只删除字符串右边的空格，左边的空格不会被删除，因此“ book ”在删除右边空格之后的结果为“ book”。

TRIM(s)删除字符串s两侧的空格。

【 例4.39 】使用TRIM()函数删除字符串两侧的空格，SQL语句如下： 

    mysql> SELECT '(  book  )',CONCAT('(', TRIM('  book  '),')');
    +------------+-----------------------------------+
    | (  book  ) | CONCAT('(', TRIM('  book  '),')') |
    +------------+-----------------------------------+
    | (  book  ) | (book)                            |
    +------------+-----------------------------------+

可以看到，函数执行之后，字符串“ book ”两边的空格都被删除，结果为“book”。

4.3.8　删除指定字符串的函数TRIM(s1 FROM s)

TRIM(s1 FROM s)删除字符串s两端所有的子字符串s1。s1为可选项，在未指定情况下，默认删除s两端的空格。

【 例4.40 】使用TRIM(s1 FROM s)函数删除字符串两端指定的字符，SQL语句如下： 

    mysql> SELECT TRIM('xy' FROM 'xyxboxyokxxyxy') ;
    +----------------------------------+
    | TRIM('xy' FROM 'xyxboxyokxxyxy') |
    +----------------------------------+
    | xboxyokx                         |
    +----------------------------------+

TRIM(s1 FROM s)函数会删除字符串“xyxboxyokxxyxy”两端的重复字符串“xy”，而中间的“xy”并不删除，因此结果为“xboxyokx”。

4.3.9　重复生成字符串的函数REPEAT(s,n)

REPEAT(s,n)返回一个由重复的字符串s组成的新字符串，字符串s的数目等于n。若n≤0，则返回一个空字符串；若s或n为NULL，则返回NULL。

【 例4.41 】使用REPEAT()函数重复生成相同的字符串，SQL语句如下： 

    mysql> SELECT REPEAT('mysql', 3);
    +--------------------+
    | REPEAT('mysql', 3) |
    +--------------------+
    | mysqlmysqlmysql    |
    +--------------------+

REPEAT('mysql', 3)函数返回由3个重复的“mysql”字符串组成的新字符串。

4.3.10　空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)

SPACE(n)返回一个由n个空格组成的字符串。

【 例4.42 】使用SPACE()函数生成由空格组成的字符串，SQL语句如下： 

    mysql> SELECT CONCAT('(', SPACE(6), ')' );
    +-----------------------------+
    | CONCAT('(', SPACE(6), ')' ) |
    +-----------------------------+
    | (      )                    |
    +-----------------------------+

SPACE(6)返回的字符串由6个空格组成。

REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。

【 例4.43 】使用REPLACE()函数进行字符串替代操作，SQL语句如下： 

    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”。

4.3.11　比较字符串大小的函数STRCMP(s1,s2)

STRCMP(s1,s2)函数用于比较字符串s1和s2的大小，若两个字符串s1和s2完全相同，则返回0；若根据当前的字符排序规则，字符串s1小于字符串s2，则返回-1；其他情况返回1。

【 例4.44 】使用STRCMP()函数比较字符串大小，SQL语句如下： 

    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。

4.3.12　获取子串的函数SUBSTRING(s,n,len)和MID(s,n,len)

SUBSTRING(s,n,len)函数返回一个从字符串s的第n位开始的、长度为len的子字符串。如果n为负值，则子字符串的起始位置是从字符串s的末尾往前数的第n个字符；如果n的值超出了字符串s的长度范围，则返回一个空字符串；如果len的值大于剩余子字符串的长度，则返回从n开始到字符串末尾的子字符串。

【 例4.45 】使用SUBSTRING()函数获取指定位置处的子字符串，SQL语句如下： 

     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)返回从字符串“breakfast”第5个位置开始到结尾的子字符串，结果为“kfast”；SUBSTRING('breakfast',5,3)返回从字符串“breakfast”第5个位置开始、长度为3的子字符串，结果为“kfa”；SUBSTRING('lunch', -3)返回从字符串“breakfast”结尾开始往前数第3个位置到字符串结尾的子字符串，结果为“nch”；SUBSTRING('lunch', -5, 3)返回从字符串“breakfast”结尾开始往前数第5个位置开始、长度为3的子字符串，结果为“lun”。

MID(s,n,len)与SUBSTRING(s,n,len)的作用相同。

【 例4.46 】使用MID()函数获取指定位置处的子字符串，SQL语句如下： 

    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的值，则结果始终为空字符串。

4.3.13　匹配子字符串开始位置的函数

LOCATE(str1,str)、POSITION(str1 IN str)和INSTR(str, str1)这3个函数的作用相同，都返回子字符串str1在字符串str中的开始位置。

【 例4.47 】使用LOCATE()、POSITION()、INSTR()函数查找字符串中指定子字符串的开始位置，SQL语句如下： 

    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。

4.3.14　字符串逆序的函数REVERSE(s)

REVERSE(s)将字符串s反转，返回的字符串的顺序和字符串s的顺序相反。

【 例4.48 】使用REVERSE()函数反转字符串，SQL语句如下： 

    mysql> SELECT REVERSE('abc');
    +--------------------+
    | REVERSE('abc')     |
    +--------------------+
    | cba                |
    +--------------------+

可以看到，字符串“abc”经过REVERSE()函数处理之后，所有字母顺序被反转，结果为“cba”。

4.3.15　返回指定位置的字符串的函数

ELT(N,字符串1,字符串2,字符串3,...,字符串N)函数返回指定位置的字符串，如果N的值等于1，则返回第1个字符串（字符串1）；如果N的值等于2，则返回第2个字符串（字符串2）；以此类推。如果N的值小于1或大于参数的总数，则返回NULL。

【 例4.49 】使用ELT()函数返回指定位置的字符串，SQL语句如下： 

    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。

4.3.16　返回指定字符串位置的函数FIELD(s,s1,s2,...,sn)

FIELD(s,s1,s2,...,sn)返回字符串s在字符串列表s1,s2,...,sn中第一次出现的位置，在找不到s的情况下，返回0。如果s为NULL，则返回值为0，原因是NULL不能同任何值进行同等比较。

【 例4.50 】使用FIELD()函数返回指定字符串第一次出现的位置，SQL语句如下： 

     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。

4.3.17　返回子字符串位置的函数FIND_IN_SET(s1,s2)

FIND_IN_SET(s1,s2)返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号（,）分开的字符串组成的列表。如果s1不在s2中或s2为空字符串，则返回值为0；如果任意一个参数为NULL，则返回值为NULL。如果这个函数的第一个参数中包含一个逗号，则该函数将无法正常运行。

【 例4.51 】使用FIND_IN_SET()函数返回子字符串在字符串列表中的位置，SQL语句如下： 

    mysql> SELECT FIND_IN_SET('Hi','hihi,Hey,Hi,bas');
    +---------------------------------------------+
    | FIND_IN_SET('Hi','hihi,Hey,Hi,bas')         |
    +---------------------------------------------+
    |                         3                   |
    +---------------------------------------------+

虽然FIND_IN_SET()和FIELD()两个函数的格式不同，但作用类似，都可以返回指定字符串在字符串列表中的位置。

4.3.18　选取字符串的函数MAKE_SET(x,s1,s2,...,sn)

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根据二进制位选取指定字符串，SQL语句如下： 

    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个字符串；MAKE_SET(1 |4,'hello','nice','world')返回从左侧开始的由第1个和第3个字符串组成的新字符串；NULL不会添加到结果中，因此MAKE_SET(1 | 4,'hello','nice',NULL,'world')只返回第1个字符串“hello”；MAKE_SET(0,'a','b','c')返回空字符串。 PK2/kNDoSob8re3ENRZkmzw1om7bccsWHphz3Jprnaz/Kyy7ZthqcS5pauObv8uE

点击中间区域
呼出菜单
上一章
目录
下一章
×