MySQL正则表达式简介

原创只是甲 2020-06-03

3380

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊MySQL的正则表达式

函数名	函数用途
REGEXP_INSTR()	指定位置开始是否符合正则表达式
REGEXP_LIKE()	判断字符串是否符合正则表达式，同义词RLIKE REGEXP
REGEXP_REPLACE()	替换满足正则表达式的字符
REGEXP_SUBSTR()	返回符合正则的字符串

一.REGEXP_INSTR()
二.REGEXP_LIKE()
三.regexp_replace()
四.regexp_substr

一.REGEXP_INSTR()

语法:

REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])
复制

-- 从第1个字符串开始查找，匹配字符','第1次出现的位置
select regexp_instr('abc,ABC,AAA,aaa',',',1,1);

-- 从第1个字符串开始查找，匹配字符','第2次出现的位置
select regexp_instr('abc,ABC,AAA,aaa',',',1,2);

-- 从第5个字符串开始查找，匹配字符','第2次出现的位置
select regexp_instr('abc,ABC,AAA,aaa',',',5,2);

-- 从第1个字符串开始查找，匹配字符','第2次出现的当前位置
select regexp_instr('abc,ABC,AAA,aaa',',',1,2,0);

-- 从第1个字符串开始查找，匹配字符','第2次出现的位置之后
select regexp_instr('abc,ABC,AAA,aaa',',',1,2,1);
复制

mysql> -- 从第1个字符串开始查找，匹配字符','第1次出现的位置
mysql> select regexp_instr('abc,ABC,AAA,aaa',',',1,1);
+-----------------------------------------+
| regexp_instr('abc,ABC,AAA,aaa',',',1,1) |
+-----------------------------------------+
|                                       4 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> -- 从第1个字符串开始查找，匹配字符','第2次出现的位置
mysql> select regexp_instr('abc,ABC,AAA,aaa',',',1,2);
+-----------------------------------------+
| regexp_instr('abc,ABC,AAA,aaa',',',1,2) |
+-----------------------------------------+
|                                       8 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> -- 从第5个字符串开始查找，匹配字符','第2次出现的位置
mysql> select regexp_instr('abc,ABC,AAA,aaa',',',5,2);
+-----------------------------------------+
| regexp_instr('abc,ABC,AAA,aaa',',',5,2) |
+-----------------------------------------+
|                                      12 |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> -- 从第1个字符串开始查找，匹配字符','第2次出现的当前位置
mysql> select regexp_instr('abc,ABC,AAA,aaa',',',1,2,0);
+-------------------------------------------+
| regexp_instr('abc,ABC,AAA,aaa',',',1,2,0) |
+-------------------------------------------+
|                                         8 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> -- 从第1个字符串开始查找，匹配字符','第2次出现的位置之后
mysql> select regexp_instr('abc,ABC,AAA,aaa',',',1,2,1);
+-------------------------------------------+
| regexp_instr('abc,ABC,AAA,aaa',',',1,2,1) |
+-------------------------------------------+
|                                         9 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>
复制

二.REGEXP_LIKE()

regexp_like语法:

regexp_like(src, pattern[, match])
复制

regexp_like是like的加强版，使用频率非常高

-- regexp_like(src,'^A') 相当于:like 'A%'
SELECT str from (
select 'A' str union all select 'AB' str union all select 'BA' str union all select 'BACD' str
) tmp
where  regexp_like(str,'^A');

-- regexp_like(src,'A$') 相当于:like '%A'
SELECT str from (
select 'A' str union all select 'AB' str union all select 'BA' str union all
select 'BACD' str
) tmp
where  regexp_like(str,'A$');

-- regexp_like(src,'^A$')相当于:like 'A'
SELECT str from (
select 'A' str union all select 'AB' str union all select 'BA' str union all
select 'BACD' str
) tmp
where  regexp_like(str,'^A$');


-- regexp_like(str,'^[ABC]')相当于： like '%A%' or like'%B%'  or like'%C%'
SELECT str
  FROM (select 'A' str union all select 'AB' str union all select 'BA' str union all
select 'BACD' str) tmp
 WHERE regexp_like(str,'^[ABC]');


-- regexp_like(src,'[0-9a-zA-Z]+') 相当于：like '%数字%' or like '%小写字母%' or like '%大写字母%'
SELECT str
  FROM (SELECT '12345' str
         
        UNION ALL
        SELECT 'abcde' str
         
        UNION ALL
        SELECT '12cde' str
         
        UNION ALL
        SELECT 'abc12' str
         
        UNION ALL
        SELECT 'a1b2cde' str
         
        UNION ALL
        SELECT '12345#' str
         
        UNION ALL
        SELECT 'abcde#' str
         ) tmp
 WHERE regexp_like(str, '^[0-9a-zA-Z]+$');


-- "+"表示匹配前面的子表达式一次或多次
SELECT str from (
select '167' str union all select '1234567' str union all select '12666' str union all select '16666' str
) tmp
where  regexp_like(str,'16+');

-- "*"表示匹配前面的子表达式零次或多次
SELECT str from (
select '167' str union all select '1234567' str union all select '12666' str union all select '16666' str
) tmp
where  regexp_like(str,'16*');
复制

mysql> -- regexp_like(src,'^A') 相当于:like 'A%'
mysql> SELECT str from (
    -> select 'A' str union all select 'AB' str union all select 'BA' str union all select 'BACD' str
    -> ) tmp
    -> where  regexp_like(str,'^A');
+-----+
| str |
+-----+
| A   |
| AB  |
+-----+
2 rows in set (0.00 sec)

mysql>
mysql> -- regexp_like(src,'A$') 相当于:like '%A'
mysql> SELECT str from (
    -> select 'A' str union all select 'AB' str union all select 'BA' str union all
    -> select 'BACD' str
    -> ) tmp
    -> where  regexp_like(str,'A$');
+-----+
| str |
+-----+
| A   |
| BA  |
+-----+
2 rows in set (0.00 sec)

mysql>
mysql> -- regexp_like(src,'^A$')相当于:like 'A'
mysql> SELECT str from (
    -> select 'A' str union all select 'AB' str union all select 'BA' str union all
    -> select 'BACD' str
    -> ) tmp
    -> where  regexp_like(str,'^A$');
+-----+
| str |
+-----+
| A   |
+-----+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> -- regexp_like(str,'^[ABC]')相当于： like '%A%' or like'%B%'  or like'%C%'
mysql> SELECT str
    ->   FROM (select 'A' str union all select 'AB' str union all select 'BA' str union all
    -> select 'BACD' str) tmp
    ->  WHERE regexp_like(str,'^[ABC]');
+------+
| str  |
+------+
| A    |
| AB   |
| BA   |
| BACD |
+------+
4 rows in set (0.00 sec)

mysql>
mysql>
mysql> -- regexp_like(src,'[0-9a-zA-Z]+') 相当于：like '%数字%' or like '%小写字母%' or like '%大写字母%'
mysql> SELECT str
    ->   FROM (SELECT '12345' str
    ->
    ->         UNION ALL
    ->         SELECT 'abcde' str
    ->
    ->         UNION ALL
    ->         SELECT '12cde' str
    ->
    ->         UNION ALL
    ->         SELECT 'abc12' str
    ->
    ->         UNION ALL
    ->         SELECT 'a1b2cde' str
    ->
    ->         UNION ALL
    ->         SELECT '12345#' str
    ->
    ->         UNION ALL
    ->         SELECT 'abcde#' str
    ->          ) tmp
    ->  WHERE regexp_like(str, '^[0-9a-zA-Z]+$');
+---------+
| str     |
+---------+
| 12345   |
| abcde   |
| 12cde   |
| abc12   |
| a1b2cde |
+---------+
5 rows in set (0.00 sec)

mysql>
mysql>
mysql> -- "+"表示匹配前面的子表达式一次或多次
mysql> SELECT str from (
    -> select '167' str union all select '1234567' str union all select '12666' str union all select '16666' str
    -> ) tmp
    -> where  regexp_like(str,'16+');
+-------+
| str   |
+-------+
| 167   |
| 16666 |
+-------+
2 rows in set (0.00 sec)

mysql>
mysql> -- "*"表示匹配前面的子表达式零次或多次
mysql> SELECT str from (
    -> select '167' str union all select '1234567' str union all select '12666' str union all select '16666' str
    -> ) tmp
    -> where  regexp_like(str,'16*');
+---------+
| str     |
+---------+
| 167     |
| 1234567 |
| 12666   |
| 16666   |
+---------+
4 rows in set (0.00 sec)

mysql>
mysql>
复制

三.regexp_replace()

regexp_replace语法:

regexp_replace(src, pattern[, replace_str[, pos[, occur[, match]]]])
复制

regexp_replace是replace的加强版，使用频率非常高

-- 去掉字符串中的AEIOU字符
select regexp_replace('ABCDEFGHI','[AEIOU]');

-- 去掉字符串中的非AEIOU字符
select regexp_replace('ABCDEFGHI','[^AEIOU]');

-- 从第5个字符开始去掉字符串中的AEIOU字符
select regexp_replace('ABCDEFGHI','[AEIOU]','',5);

-- 从第5个字符开始去掉第二次出现字符串中的AEIOU字符
select regexp_replace('ABCDEFGHI','[AEIOU]','',5,2);

复制

mysql> -- 去掉字符串中的AEIOU字符
mysql> select regexp_replace('ABCDEFGHI','[AEIOU]');
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'regexp_replace'
mysql>
mysql> -- 去掉字符串中的非AEIOU字符
mysql> select regexp_replace('ABCDEFGHI','[^AEIOU]');
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'regexp_replace'
mysql>
mysql> -- 从第5个字符开始去掉字符串中的AEIOU字符
mysql> select regexp_replace('ABCDEFGHI','[AEIOU]','',5);
+--------------------------------------------+
| regexp_replace('ABCDEFGHI','[AEIOU]','',5) |
+--------------------------------------------+
| ABCDFGH                                    |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> -- 从第5个字符开始去掉第二次出现字符串中的AEIOU字符
mysql> select regexp_replace('ABCDEFGHI','[AEIOU]','',5,2);
+----------------------------------------------+
| regexp_replace('ABCDEFGHI','[AEIOU]','',5,2) |
+----------------------------------------------+
| ABCDEFGH                                     |
+----------------------------------------------+
1 row in set (0.00 sec)
复制

四.regexp_substr

regexp_substr语法:

regexp_substr(src, pattern[, pos[, occur[, match[, subexpr]]]])
复制

regexp_substr就是substr的加强版

-- 用正则分隔的第一个值是192
SELECT REGEXP_SUBSTR('192.168.16.18','[^.]+',1,1,'i') AS STR;  

-- 拆分IP
SELECT regexp_substr(ip, '[^.]+', 1, 1) a,
       regexp_substr(ip, '[^.]+', 1, 2) b,
       regexp_substr(ip, '[^.]+', 1, 3) c,
       regexp_substr(ip, '[^.]+', 1, 4) d
  FROM (SELECT '192.168.16.18' AS ip FROM dual) tmp;
复制

mysql> SELECT REGEXP_SUBSTR('192.168.16.18','[^.]+',1,1,'i') AS STR;
+-----+
| STR |
+-----+
| 192 |
+-----+
1 row in set (0.00 sec)

mysql>
mysql> SELECT regexp_substr(ip, '[^.]+', 1, 1) a,
    ->        regexp_substr(ip, '[^.]+', 1, 2) b,
    ->        regexp_substr(ip, '[^.]+', 1, 3) c,
    ->        regexp_substr(ip, '[^.]+', 1, 4) d
    ->   FROM (SELECT '192.168.16.18' AS ip FROM dual) tmp;
+-----+-----+----+----+
| a   | b   | c  | d  |
+-----+-----+----+----+
| 192 | 168 | 16 | 18 |
+-----+-----+----+----+
1 row in set (0.00 sec)
复制