爱站程序员基地文章目录
- 一.正则表达式集合及操作符
- 1.字符集合:
- 2.边界集合:
- 3.重复次数集合:
- 4.组合操作符:
- 5.匹配操作符:
- 6.转义操作符:
- 1.regexp_count
- 2.regexp_replace
- 3.regexp_like
- 4.regexp_substr
- 5.regexp_instr
备注:测试数据库版本为Oracle 11g R2
这个blog我们来聊聊Oracle的正则表达式
一.正则表达式集合及操作符
1.字符集合:
| 字符 | 匹配的字符 |
|---|---|
| \\d | 从０-９的任一数字 |
| \\D | 任一非数字字符 |
| \\w | 任一单词字符，包括A-Z,a-z,0-9和下划线 |
| \\W | 任一非单词字符 |
| \\s | 任一空白字符，包括制表符，换行符，回车符，换页符和垂直制表符 |
| \\S | 任一非空白字符 |
| . | 任一字符 |
字符簇:
[[:alpha:]] 任何字母。[[:digit:]] 任何数字。[[:alnum:]] 任何字母和数字。[[:space:]] 任何空白字符。[[:upper:]] 任何大写字母。[[:lower:]] 任何小写字母。[[:punct:]] 任何标点符号。[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。
2.边界集合:
| 字符 l | 描述 |
|---|---|
| ^ | 每一行的开头，单行模式下等价于字符串的开头 |
| $ | 每一行的结尾，单行模式下等价于字符串的结尾 |
3.重复次数集合:
贪婪模式会获取尽可能多的字符，而非贪婪模式会获取尽可能少的字符。
| 贪婪 | 非贪婪 | 描述 |
|---|---|---|
| * | *? | 零次或多次 |
| ? | ?? | 零次或一次 |
| + | +? | 一次或多次 |
| {m} | {m}? | 正好m次，贪婪与非贪婪一样的 |
| {m,} | {m,}? | 至少m次 |
| {m, n} | {m, n}? | 最少m最多n次 |
4.组合操作符:
优先级比较：圆括号>重复次数操作符>和>或
| 组合操作符 | 描述 |
|---|---|
| […] | 方括号内任意字符或字符集合中的一个 |
| [^…] | 方括号内^为第一个字符时，表示与其后所有字符都不匹配的字符 |
| (…) | 圆括号，将复杂表达式当作单一表达式来处理 |
| …|… | 或 |
| abc | 和。直接将字符连在一起写 |
5.匹配操作符:
| 匹配操作符 | 描述 |
|---|---|
| \\n | 即后向引用。n为1~9，标识由圆括号里取得的匹配字符串。方向是从左到右在regexp_replace函数中，允许在模式表达式和替换表达式中都使用\\n |
6.转义操作符:
| 转义操作符 | 描述 |
|---|---|
| \\ | 将其后紧跟着的操作字符当作普通字符看待。例如 abcdef 可以匹配 abdef或abcccdef等，但无法匹配 abcdef，后者需要abc*def才能匹配 |
二.正则表达式语法
regexp_count(src, pattern[, pos[, match]])regexp_instr(src, pattern[, pos[, occur[, retopt[, match[, subexpr]]]]])regexp_like(src, pattern[, match])regexp_substr(src, pattern[, pos[, occur[, match[, subexpr]]]])regexp_replace(src, pattern[, replace_str[, pos[, occur[, match]]]])
参数释义:
– src：需要用来进行分析的字符串，如 \"aaaa\",\"welcome\",\"abc12cdg\" 等;– pattern：要匹配的正则表达式;– pos：从源字符串的第几个字符开始匹配;– occur：指定pattern的第几次出现;– retopt：返回选项，regexp_instr专用。用于设置返回值是pattern出现之时的位置还是之后的位置。 0代表前者，1代表后者;– replace_str：要替换成的字符串，可以使用正则表达式中的后向引用操作符;– subexpr：regexp_instr和regexp_substr特有。指定返回pattern的第几个子表达式。 0表示返回整个pattern匹配的字符串。例如0123(((abc)(de)f)ghi)45(678)就包含abcdefghi、 abcdef、 abc、 de、 678这五个子串（已按顺序排列）;– match：匹配参数配置，让正则更易用。‘i’ 用于不区分大小写的匹配‘c’ 用于区分大小写的匹配‘n’ 允许句点(.)作为通配符去匹配换行符。如果省略该参数，则句点将不匹配换行符‘m’ 将源串视为多行。即Oracle 将^和$分别看作源串中任意位置任何行的开始和结束，而不是仅仅看作整个源串的开始或结束。如果省略该参数，则Oracle将源串看作一行。‘x’ 忽略空格字符。默认情况下，空格字符与自身相匹配。
三.正则表达式功能
1.regexp_count
regexp_count语法:
regexp_count(src, pattern[, pos[, match]])
regexp_count用于求原字符串中某个匹配字符出现的次数
--匹配字符串中某个字符出现的次数SELECT REGEXP_COUNT (\'aaa,bbb,ccc,ddd,eee\', \',\') AS cot FROM DUAL;--遇到通配符 要使用 \\ 转义SELECT REGEXP_COUNT (\'aaa$,bbb$,ccc$,ddd$,eee\', \'\\$,\') AS cot FROM DUAL;--从第10个字符串开始匹配SELECT REGEXP_COUNT (\'aaa,bbb,ccc,ddd,eee\', \',\',10) AS cot FROM DUAL;--默认是区分大小写的匹配SELECT REGEXP_COUNT (\'abc,ABC,AAA,aaa\', \'a\') AS cot FROM DUAL;--设置成不区分大小写的匹配SELECT REGEXP_COUNT (\'abc,ABC,AAA,aaa\', \'a\',1,\'i\') AS cot FROM DUAL;
SQL> --匹配字符串中某个字符出现的次数SQL> SELECT REGEXP_COUNT (\'aaa,bbb,ccc,ddd,eee\', \',\') AS cot FROM DUAL;COT----------4SQL> --遇到通配符 要使用 \\ 转义SQL> SELECT REGEXP_COUNT (\'aaa$,bbb$,ccc$,ddd$,eee\', \'\\$,\') AS cot FROM DUAL;COT----------4SQL> --从第10个字符串开始匹配SQL> SELECT REGEXP_COUNT (\'aaa,bbb,ccc,ddd,eee\', \',\',10) AS cot FROM DUAL;COT----------2SQL> --默认是区分大小写的匹配SQL> SELECT REGEXP_COUNT (\'abc,ABC,AAA,aaa\', \'a\') AS cot FROM DUAL;COT----------4SQL> --设置成不区分大小写的匹配SQL> SELECT REGEXP_COUNT (\'abc,ABC,AAA,aaa\', \'a\',1,\'i\') AS cot FROM DUAL;COT----------8
2.regexp_replace
regexp_replace语法:
regexp_replace(src, pattern[, replace_str[, pos[, occur[, match]]]])
regexp_replace是replace的加强版，使用频率非常高
--去掉字符串中的AEIOU字符select regexp_replace(\'ABCDEFGHI\',\'[AEIOU]\') from dual;--去掉字符串中的非AEIOU字符select regexp_replace(\'ABCDEFGHI\',\'[^AEIOU]\') from dual;--从第5个字符开始去掉字符串中的AEIOU字符select regexp_replace(\'ABCDEFGHI\',\'[AEIOU]\',\'\',5) from dual;--从第5个字符开始去掉第二次出现字符串中的AEIOU字符select regexp_replace(\'ABCDEFGHI\',\'[AEIOU]\',\'\',5,2) from dual;--拓展1 取名字中的大写字母SELECT regexp_replace(data, \'([[:upper:]])(.*)([[:upper:]])(.*)\', \'\\1.\\3\')FROM (SELECT \'Bill Gates\' dataFROM dualUNION ALLSELECT \'Michael Jordan\' dataFROM dualUNION ALLSELECT \'Kobe Bryant\' dataFROM dual);
SQL> --去掉字符串中的AEIOU字符SQL> select regexp_replace(\'ABCDEFGHI\',\'[AEIOU]\') from dual;REGEXP_REPLACE(\'ABCDEFGHI\',\'[A------------------------------BCDFGHSQL> --去掉字符串中的非AEIOU字符SQL> select regexp_replace(\'ABCDEFGHI\',\'[^AEIOU]\') from dual;REGEXP_REPLACE(\'ABCDEFGHI\',\'[^------------------------------AEISQL> --从第5个字符开始去掉字符串中的AEIOU字符SQL> select regexp_replace(\'ABCDEFGHI\',\'[AEIOU]\',\'\',5) from dual;REGEXP_REPLACE(\'ABCDEFGHI\',\'[A------------------------------ABCDFGHSQL> --从第5个字符开始去掉第二次出现字符串中的AEIOU字符SQL> select regexp_replace(\'ABCDEFGHI\',\'[AEIOU]\',\'\',5,2) from dual;REGEXP_REPLACE(\'ABCDEFGHI\',\'[A------------------------------ABCDEFGHSQL> --拓展1 取名字中的大写字母SQL> SELECT regexp_replace(data, \'([[:upper:]])(.*)([[:upper:]])(.*)\', \'\\1.\\3\')2 FROM (SELECT \'Bill Gates\' data3 FROM dual4 UNION ALL5 SELECT \'Michael Jordan\' data6 FROM dual7 UNION ALL8 SELECT \'Kobe Bryant\' data9 FROM dual);REGEXP_REPLACE(DATA,\'([[:UPPER--------------------------------------------------------------------------------B.GM.JK.B
3.regexp_like
regexp_like语法:
regexp_like(src, pattern[, match])
regexp_like是like的加强版，使用频率非常高
--regexp_like(src,\'^A\') 相当于:like \'A%\'SELECT str from (select \'A\' str from dual union all select \'AB\' str from dual union all select \'BA\' str from dual union all select \'BACD\' str from dual)where regexp_like(str,\'^A\');--regexp_like(src,\'A$\') 相当于:like \'%A\'SELECT str from (select \'A\' str from dual union all select \'AB\' str from dual union all select \'BA\' str from dual union allselect \'BACD\' str from dual)where regexp_like(str,\'A$\');--regexp_like(src,\'^A$\')相当于:like \'A\'SELECT str from (select \'A\' str from dual union all select \'AB\' str from dual union all select \'BA\' str from dual union allselect \'BACD\' str from dual)where regexp_like(str,\'^A$\');--regexp_like(str,\'^[ABC]\')相当于： like \'%A%\' or like\'%B%\' or like\'%C%\'SELECT strFROM (select \'A\' str from dual union all select \'AB\' str from dual union all select \'BA\' str from dual union allselect \'BACD\' str from dual)WHERE regexp_like(str,\'^[ABC]\');--regexp_like(src,\'[0-9a-zA-Z]+\') 相当于：like \'%数字%\' or like \'%小写字母%\' or like \'%大写字母%\'SELECT strFROM (SELECT \'12345\' strFROM dualUNION ALLSELECT \'abcde\' strFROM dualUNION ALLSELECT \'12cde\' strFROM dualUNION ALLSELECT \'abc12\' strFROM dualUNION ALLSELECT \'a1b2cde\' strFROM dualUNION ALLSELECT \'12345#\' strFROM dualUNION ALLSELECT \'abcde#\' strFROM dual)WHERE regexp_like(str, \'^[0-9a-zA-Z]+$\');--\"+\"表示匹配前面的子表达式一次或多次SELECT str from (select \'167\' str from dual union all select \'1234567\' str from dual union all select \'12666\' str from dual union all select \'16666\' str from dual)where regexp_like(str,\'16+\');--\"*\"表示匹配前面的子表达式零次或多次SELECT str from (select \'167\' str from dual union all select \'1234567\' str from dual union all select \'12666\' str from dual union all select \'16666\' str from dual)where regexp_like(str,\'16*\');
SQL> --regexp_like(src,\'^A\') 相当于:like \'A%\'SQL> SELECT str from (2 select \'A\' str from dual union all select \'AB\' str from dual union all select \'BA\' str from dual union all select \'BACD\' str from dual3 )4 where regexp_like(str,\'^A\');STR----AABSQL> --regexp_like(src,\'A$\') 相当于:like \'%A\'SQL> SELECT str from (2 select \'A\' str from dual union all select \'AB\' str from dual union all select \'BA\' str from dual union all3 select \'BACD\' str from dual4 )5 where regexp_like(str,\'A$\');STR----ABASQL> --regexp_like(src,\'^A$\')相当于:like \'A\'SQL> SELECT str from (2 select \'A\' str from dual union all select \'AB\' str from dual union all select \'BA\' str from dual union all3 select \'BACD\' str from dual4 )5 where regexp_like(str,\'^A$\');STR----ASQL> --regexp_like(str,\'^[ABC]\')相当于： like \'%A%\' or like\'%B%\' or like\'%C%\'SQL> SELECT str2 FROM (select \'A\' str from dual union all select \'AB\' str from dual union all select \'BA\' str from dual union all3 select \'BACD\' str from dual)4 WHERE regexp_like(str,\'^[ABC]\');STR----AABBABACDSQL> --regexp_like(src,\'[0-9a-zA-Z]+\') 相当于：like \'%数字%\' or like \'%小写字母%\' or like \'%大写字母%\'SQL> SELECT str2 FROM (SELECT \'12345\' str3 FROM dual4 UNION ALL5 SELECT \'abcde\' str6 FROM dual7 UNION ALL8 SELECT \'12cde\' str9 FROM dual10 UNION ALL11 SELECT \'abc12\' str12 FROM dual13 UNION ALL14 SELECT \'a1b2cde\' str15 FROM dual16 UNION ALL17 SELECT \'12345#\' str18 FROM dual19 UNION ALL20 SELECT \'abcde#\' str21 FROM dual)22 WHERE regexp_like(str, \'^[0-9a-zA-Z]+$\');STR-------12345abcde12cdeabc12a1b2cdeSQL> --\"+\"表示匹配前面的子表达式一次或多次SQL> SELECT str from (2 select \'167\' str from dual union all select \'1234567\' str from dual union all select \'12666\' str from dual union all select \'16666\' str from dual3 )4 where regexp_like(str,\'16+\');STR-------16716666SQL> --\"*\"表示匹配前面的子表达式零次或多次SQL> SELECT str from (2 select \'167\' str from dual union all select \'1234567\' str from dual union all select \'12666\' str from dual union all select \'16666\' str from dual3 )4 where regexp_like(str,\'16*\');STR-------16712345671266616666
4.regexp_substr
regexp_substr语法:
regexp_substr(src, pattern[, pos[, occur[, match[, subexpr]]]])
regexp_substr就是substr的加强版
--用正则分隔的第一个值是192SELECT REGEXP_SUBSTR(\'192.168.16.18\',\'[^.]+\',1,1,\'i\') AS STR FROM DUAL;--拆分IPSELECT regexp_substr(ip, \'[^.]+\', 1, 1) a,regexp_substr(ip, \'[^.]+\', 1, 2) b,regexp_substr(ip, \'[^.]+\', 1, 3) c,regexp_substr(ip, \'[^.]+\', 1, 4) dFROM (SELECT \'192.168.16.18\' AS ip FROM dual);--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) dFROM (SELECT \'192.168.16.18\' AS ip FROM dual);--用regexp_substr进行字符串行转列--在不知道分隔符有多少的情况下,可以这么使用，但是有控制with tmp1 as(select level lvl from dual connect by level <=10),tmp2 as(SELECT \'192.168.16.18\' AS ip FROM dual)select regexp_substr(ip, \'[^.]+\', 1, lvl)from tmp2,tmp1;--这样可以去掉空值withtmp2 as(SELECT \'192.168.16.18\' AS ip FROM dual)select regexp_substr(ip, \'[^.]+\', 1, level)from tmp2,dualconnect by level <= regexp_count(ip,\'\\.\') + 1;
SQL> --用正则分隔的第一个值是192SQL> SELECT REGEXP_SUBSTR(\'192.168.16.18\',\'[^.]+\',1,1,\'i\') AS STR FROM DUAL;STR---192SQL> --拆分IPSQL> SELECT regexp_substr(ip, \'[^.]+\', 1, 1) a,2 regexp_substr(ip, \'[^.]+\', 1, 2) b,3 regexp_substr(ip, \'[^.]+\', 1, 3) c,4 regexp_substr(ip, \'[^.]+\', 1, 4) d5 FROM (SELECT \'192.168.16.18\' AS ip FROM dual);A B C D--- --- -- --192 168 16 18SQL> --ip进行拆分SQL> SELECT regexp_substr(ip, \'[^.]+\', 1, 1) a,2 regexp_substr(ip, \'[^.]+\', 1, 2) b,3 regexp_substr(ip, \'[^.]+\', 1, 3) c,4 regexp_substr(ip, \'[^.]+\', 1, 4) d5 FROM (SELECT \'192.168.16.18\' AS ip FROM dual);A B C D--- --- -- --192 168 16 18SQL> --用regexp_substr进行字符串行转列SQL> --在不知道分隔符有多少的情况下,可以这么使用，但是有控制SQL> with tmp1 as2 (select level lvl from dual connect by level <=10),3 tmp2 as4 (SELECT \'192.168.16.18\' AS ip FROM dual5 )6 select regexp_substr(ip, \'[^.]+\', 1, lvl)7 from tmp2,tmp1;REGEXP_SUBSTR(IP,\'[^.]+\',1,LVL------------------------------192168161810 rows selectedSQL> --这样可以去掉空值SQL> with2 tmp2 as3 (SELECT \'192.168.16.18\' AS ip FROM dual)4 select regexp_substr(ip, \'[^.]+\', 1, level)5 from tmp2,dual6 connect by level <= regexp_count(ip,\'\\.\') + 17 ;REGEXP_SUBSTR(IP,\'[^.]+\',1,LEV------------------------------1921681618
5.regexp_instr
regexp_instr语法:
regexp_instr(src, pattern[, pos[, occur[, retopt[, match[, subexpr]]]]])
regexp_instr是instr的加强版本
--从第1个字符串开始查找，匹配字符\',\'第1次出现的位置select regexp_instr(\'abc,ABC,AAA,aaa\',\',\',1,1) from dual;--从第1个字符串开始查找，匹配字符\',\'第2次出现的位置select regexp_instr(\'abc,ABC,AAA,aaa\',\',\',1,2) from dual;--从第5个字符串开始查找，匹配字符\',\'第2次出现的位置select regexp_instr(\'abc,ABC,AAA,aaa\',\',\',5,2) from dual;--从第1个字符串开始查找，匹配字符\',\'第2次出现的当前位置select regexp_instr(\'abc,ABC,AAA,aaa\',\',\',1,2,0) from dual;--从第1个字符串开始查找，匹配字符\',\'第2次出现的位置之后select regexp_instr(\'abc,ABC,AAA,aaa\',\',\',1,2,1) from dual;
SQL> --从第1个字符串开始查找，匹配字符\',\'第1次出现的位置SQL> select regexp_instr(\'abc,ABC,AAA,aaa\',\',\',1,1) from dual;REGEXP_INSTR(\'ABC,ABC,AAA,AAA\'------------------------------4SQL> --从第1个字符串开始查找，匹配字符\',\'第2次出现的位置SQL> select regexp_instr(\'abc,ABC,AAA,aaa\',\',\',1,2) from dual;REGEXP_INSTR(\'ABC,ABC,AAA,AAA\'------------------------------8SQL> --从第5个字符串开始查找，匹配字符\',\'第2次出现的位置SQL> select regexp_instr(\'abc,ABC,AAA,aaa\',\',\',5,2) from dual;REGEXP_INSTR(\'ABC,ABC,AAA,AAA\'------------------------------12SQL> --从第1个字符串开始查找，匹配字符\',\'第2次出现的当前位置SQL> select regexp_instr(\'abc,ABC,AAA,aaa\',\',\',1,2,0) from dual;REGEXP_INSTR(\'ABC,ABC,AAA,AAA\'------------------------------8SQL> --从第1个字符串开始查找，匹配字符\',\'第2次出现的位置之后SQL> select regexp_instr(\'abc,ABC,AAA,aaa\',\',\',1,2,1) from dual;REGEXP_INSTR(\'ABC,ABC,AAA,AAA\'------------------------------9
