【Oracle】Oracle系列之十--Oracle正则表达式

朴灿烈づ我的快乐病毒、 2023-10-16 15:40 87阅读 0赞

#### 文章目录 ####

*  往期回顾
 *  前言
 *  1. 基本语法
 *   *  （1）字符匹配
     *  （2）限定符
     *  （3）边界匹配
     *  （4）分组和引用
     *  （5）字符集合
     *  （6）转义符
 *  2. POSIX字符类
 *  3. 正则表达式函数
 *  4. 常用正则表达式

## 往期回顾 ##

*  [【Oracle】Oracle系列–Oracle数据类型][Oracle_Oracle_Oracle]
 *  [【Oracle】Oracle系列之二–Oracle数据字典][Oracle_Oracle_Oracle 1]
 *  [【Oracle】Oracle系列之三–Oracle字符集][Oracle_Oracle_Oracle 2]
 *  [【Oracle】Oracle系列之四–用户管理][Oracle_Oracle]
 *  [【Oracle】Oracle系列之五–Oracle表空间][Oracle_Oracle_Oracle 3]
 *  [【Oracle】Oracle系列之六–Oracle表分区][Oracle_Oracle_Oracle 4]
 *  [【Oracle】Oracle系列之七–表的创建与管理][Oracle_Oracle 1]
 *  [【Oracle】Oracle系列之八–SQL查询][Oracle_Oracle_SQL]
 *  [【Oracle】Oracle系列之九–Oracle常用函数][Oracle_Oracle_Oracle 5]

## 前言 ##

正则表达式 (Regular expression) 是一种强大的文本处理工具，Oracle数据库自9i版本开始引入了正则表达式支持，可帮助开发者快速而准确地匹配、查找和替换字符串，广泛应用于数据查询、数据分析、数据清洗等领域。

## 1. 基本语法 ##

Oracle正则表达式的语法基于Perl语言的正则表达式语法，区分大小写（case sensitive）。

### （1）字符匹配 ###

.：匹配除了换行外的任意一个字符；  
\\d：匹配任何数字，相当于 \[0-9\]；  
\\D：匹配任何非数字字符，相当于 \[^0-9\]；  
\\w：匹配任何字母数字字符或下划线，相当于 \[a-zA-Z0-9\_\]；  
\\W：表示匹配任何非字母数字字符或下划线，相当于 \[^a-zA-Z0-9\_\]。

### （2）限定符 ###

\*：匹配前一个字符出现0次或多次；  
\+：匹配前一个字符出现1次或多次；  
?：匹配前一个字符出现0次或1次；  
\{n\}：匹配前一个字符出现n次；  
\{n,\}：匹配前一个字符出现n次或更多；  
\{n,m\}：匹配前一个字符出现n~m次。  
‘|’ ：指两项之间的一个选择。

e.g.

^(\[a-z\]+|\[0-9\]+)$：表示所有小写字母或数字组合成的字符串。

### （3）边界匹配 ###

^：匹配开始位置；  
$：匹配结束位置；  
\\b：匹配单词边界，即单词的开头或结尾位置；  
\\B：匹配非单词边界，即不是单词的开头或结尾位置。

### （4）分组和引用 ###

()：分组，标记一个子表达式的开始和结束位置；  
\\num：引用第num个子表达式，num从1开始。

### （5）字符集合 ###

\[\]：表示一组字符中的任意一个。

### （6）转义符 ###

\\ \\：表示转义一个字符。  
oracle正则表达式还支持一些高级语法，例如贪婪匹配、非贪婪匹配、零宽断言(zero-width assertion)、后向引用(backreference)、捕获组等。

## 2. POSIX字符类 ##

Oracle数据库中的POSIX字符类是一组特殊的字符类，用于在正则表达式中匹配特定的字符。

POSIX字符类以 \[: 开头，以 :\] 结尾，中间包含一个或多个字符，代表特定的字符集合。POSIX字符类中的字符集合可以是预定义的，也可以是自定义的。

\[\[:alpha:\]\] 任何字母，等同于字符集合 \[a-zA-Z\]；  
\[\[:digit:\]\] 任何数字，等同于字符集合 \[0-9\]；  
\[\[:alnum:\]\] 任何字母和数字，等同于字符集合 \[a-zA-Z0-9\]；  
\[\[:space:\]\] 任何白字符；  
\[\[:upper:\]\] 任何大写字母；  
\[\[:lower:\]\] 任何小写字母；  
\[\[:punct:\]\] 任何标点符号；  
\[\[:xdigit:\]\] 任何16进制的数字，相当于\[0-9a-fA-F\]。  
Oracle数据库中，POSIX字符类可以用于各种正则表达式相关的操作，如模式匹配、替换、分割等。由于Oracle数据库中的POSIX字符类与其他数据库或编程语言中的POSIX字符类可能略有不同，具体使用时需要查看相关文档。

## 3. 正则表达式函数 ##

Oracle数据库提供了多种正则表达式函数，可以对文本数据进行匹配、替换等操作。

REGEXP\_LIKE: 判断字符串是否匹配指定的正则表达式。  
e.g. 查询员工名字以"S"开头，以"n"结尾的记录：

SELECT * FROM emp WHERE REGEXP_LIKE(emp_name, '^S.*n$');

REGEXP\_REPLACE: 替换字符串中的子串。  
e.g. 将字符串"12345"中连续的三个数字替换成星号"\*"，输出  
“\*45”：

SELECT REGEXP_REPLACE('12345', '\d{3}', '*') FROM dual;

REGEXP\_SUBSTR: 提取字符串中匹配指定正则表达式的子串。  
e.g. 从字符串"abc 123 def"中提取出连续的数字"123"：

SELECT REGEXP_SUBSTR('abc 123 def', '\d+') FROM dual;

REGEXP\_INSTR: 返回字符串中匹配指定正则表达式的子串的位置。  
e.g. 返回字符串"1ab2cd3ef"中第一个连续数字的起始位置，即1

SELECT REGEXP_INSTR('1ab2cd3ef', '\d+') FROM dual;

## 4. 常用正则表达式 ##

– 查询value中不是纯数字的记录

select * from employee where not regexp_like(value,'^[[:digit:]]+$');

– 查询value中不包含任何数字的记录。

select * from employee where regexp_like(value,'^[^[:digit:]]+$');

–查询所有包含小写字母或者数字的记录。

select * from employee where regexp_like(value,'^([a-z]+|[0-9]+)$');

– 提取字符串中的数字

create or replace function EXTRACT_NUMBER(STR in varchar2) return varchar2 is
      POSITION number;
      STR_EXT varchar2(800);
      STR_TMP varchar2(800);
      result  varchar2(800);
    begin
      STR_EXT:=STR;
      POSITION:=1;
      LOOP
        STR_TMP:=REGEXP_SUBSTR(STR_EXT,'([0-9]+)',POSITION);
        result:=result||STR_TMP;
        STR_EXT:= SUBSTR(STR_EXT,POSITION+LENGTH(STR_TMP),LENGTH(STR));
        POSITION:=regexp_instr(STR_EXT,'([0-9]+)',1);
      exit when POSITION is null or POSITION=0;
      end loop;
      return(result);
    end EXTRACT_NUMBER;

[Oracle_Oracle_Oracle]: https://writer.blog.csdn.net/article/details/132955822
[Oracle_Oracle_Oracle 1]: https://writer.blog.csdn.net/article/details/132997299
[Oracle_Oracle_Oracle 2]: https://writer.blog.csdn.net/article/details/133047093
[Oracle_Oracle]: https://writer.blog.csdn.net/article/details/133062052
[Oracle_Oracle_Oracle 3]: https://writer.blog.csdn.net/article/details/133148656
[Oracle_Oracle_Oracle 4]: https://writer.blog.csdn.net/article/details/133156311
[Oracle_Oracle 1]: https://writer.blog.csdn.net/article/details/133190196
[Oracle_Oracle_SQL]: https://writer.blog.csdn.net/article/details/133230775
[Oracle_Oracle_Oracle 5]: https://blog.csdn.net/u011397981/article/details/133279884?spm=1001.2014.3001.5501