MySQL常用字符串函数大全（附带实例）

字符串函数主要用来处理数据库中的字符串数据。MySQL 中的字符串函数有计算字符串长度函数、字符串合并函数、字符串替换函数、字符串比较函数、查找指定字符串位置函数等。

本节将介绍 MySQL 各种字符串函数的功能和用法。

MySQL常用字符串函数

1) MySQL计算字符串的字符数和长度

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

【实例】使用 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 字节。

【实例】使用 LENGTH() 函数计算字符串长度，SQL语句如下：

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

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

2) MySQL合并字符串

CONCAT(s1,s2,...) 的返回结果为所有参数相连而产生的新字符串，或许有一个或多个参数：

如果任何一个参数为 NULL，则返回值为 NULL；
如果所有参数均为非二进制字符串，则结果为非二进制字符串；
如果参数中含有二进制字符串，则结果为一个二进制字符串。

【实例】使用 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。

【实例】使用 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。

3) MySQL替换字符串

NSERT(s1,x,len,s2) 返回一个新的字符串，其内容是将字符串 s1 中从位置 x 开始的 len 个字符替换为字符串 s2 的内容：

如果 x 超过字符串 s1 的长度，则返回值为原始字符串；
如果 len 的长度超出了字符串 s1 的长度，则从位置 x 开始替换到 s1 的末尾；
若任何一个参数为 NULL，则返回值为 NULL。

【实例】使用 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) MySQL字母大小写转换

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

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

mysql> SELECT LOWER('BEAUTIFUL'), LCASE('Well');
+--------------------+---------------+
| LOWER('BEAUTIFUL') | LCASE('Well') |
+--------------------+---------------+
| beautiful          | well          |
+--------------------+---------------+

由结果可以看到，原来所有字母为大写的，都被转换为小写，如“BEAUTIFUL”转换之后为“beautiful”；大小写字母混合的字符串，小写不变，大写字母转换为小写字母，如“WelL”转换之后为“well”。

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

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

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

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

5) MySQL获取指定长度的字符串

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

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

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

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

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

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

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

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

6) MySQL填充字符串

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

【实例】使用 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 个字符。

【实例】使用 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。

7) MySQL删除空格

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

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

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

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

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

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

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

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

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

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

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

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

8) MySQL删除指定字符串

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

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

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

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

9) MySQL重复生成字符串

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

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

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

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

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

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

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

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

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

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

【实例】使用 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”。

11) MySQL比较字符串大小

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

【实例】使用 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。

12) MySQL获取子串

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

【实例】使用 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) 的作用相同。

【实例】使用 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 的值，则结果始终为空字符串。

13) MySQL匹配子字符串开始位置

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

【实例】使用 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。

14) MySQL字符串逆序

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

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

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

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

15) MySQL返回指定位置的字符串

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

【实例】使用 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。

16) MySQL返回指定字符串的位置

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

【实例】使用 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。

17) MySQL返回子字符串位置

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

【实例】使用 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() 两个函数的格式不同，但作用类似，都可以返回指定字符串在字符串列表中的位置。

18) MySQL选取字符串

MAKE_SET(x,s1,s2,...,sn) 函数按 x 的二进制数从 s1,s2,...,sn 中选取字符串。例如 5 的二进制值是 0101，这个二进制从右往左的第 1 位和第 3 位是 1，所以选取 s1 和 s3。s1,s2,...,sn 中的 NULL 不会被添加到结果中。

【实例】使用 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') 返回空字符串。

MySQL常用字符串函数大全（附带实例）