Oracle中的正则函数

素颜马尾好姑娘i 2024-03-17 09:38 70阅读 0赞

**目录**

一、概述

二、正则的元字符

2.1匹配字符的元字符

2.2数量词

2.3其他元字符

2.4支持的字符类

三、正则表达式函数

3.1regexp\_like

3.2regexp\_replace

3.3regexp\_instr

3.4regexp\_substr

3.5regexp\_count

--------------------

## 一、概述 ##

正则函数是一个用于处理文本字符串并返回指定模式匹配结果的函数集合，它可以通过正则表达式来实现灵活高效的字符串操作。例如使用元字符、限定符、字符集等，并根据实际需求构造所需的表达式。然后，可以将正则表达式作为参数传递给具体的函数，并指定要操作的字符串。最后，按照函数的返回值或影响行数进行处理。

正则函数在数据清洗、格式化、搜索等场景下广泛应用，特别是当涉及到需要匹配复杂模式或批量处理数据时，正则函数的作用尤为重要。

## 二、正则的元字符 ##

### 2.1匹配字符的元字符 ###

<table style="width:500px;">  表一  
 <tbody> 
  <tr> 
   <td>[...]</td> 
   <td>方括号定义了一个匹配列表，可以匹配罗列在方括号中的任意一个字符。可以指定字符的范围，比如a~z。这个范围是根据NLS_SORT的设置进行解释的。<br> 一个出现在列表开头或者结尾的破折号(-)（比如[abc-]是一个直接量。一个出现在列表开头的闭括号（]）是个直接量（比如[]abc)。如果脱字符号^出现在列表的开头，这个列表就变成了<strong><span style="color:#fe2c24;">不匹配列表</span></strong>（见下一条）</td> 
  </tr> 
  <tr> 
   <td>[^...]</td> 
   <td>可以和任何一个不在这个方括号列表中的字符相匹配，也叫做<strong><span style="color:#fe2c24;">不匹配列表</span></strong></td> 
  </tr> 
  <tr> 
   <td>[:class:]</td> 
   <td>可以和指定的字符类的任何一个字符相匹配。只可以用在匹配列表中【也就是说这种表示法只能出现在另一对方括号之内】：[[:class:]abc]是一个合法的表达式，[:class:]abc不合法。表4列出了合法的字符类名字</td> 
  </tr> 
  <tr> 
   <td>[.coll.]</td> 
   <td>可以匹配指定的校对元素，这个元素可以是一个或者多个字符。只可以用在匹配列表中。例如，[[.ch.]]匹配西班牙字母“ch”。</td> 
  </tr> 
  <tr> 
   <td>[=char=]</td> 
   <td>可以匹配来自于同一个基础字符的衍生字符。只能用在匹配列<br> 表中。</td> 
  </tr> 
  <tr> 
   <td>\d</td> 
   <td>匹配任何一个数字，等价于[[::digit:]]</td> 
  </tr> 
  <tr> 
   <td>\D</td> 
   <td>匹配任何非数字内容，等价于[^[:digit:]]</td> 
  </tr> 
  <tr> 
   <td>\w</td> 
   <td>匹配任何“单词字符”，单词字符包括字母、数字、下划线</td> 
  </tr> 
  <tr> 
   <td>\W</td> 
   <td>匹配任何非单词字符</td> 
  </tr> 
  <tr> 
   <td>\s</td> 
   <td>匹配任何空白字符，等价于[[:space:]]</td> 
  </tr> 
  <tr> 
   <td>\S</td> 
   <td>匹配任何非空白字符，等价于[^[:space:]]</td> 
  </tr> 
 </tbody> 
</table>

### 2.2数量词 ###

<table style="width:500px;">  表二  
 <tbody> 
  <tr> 
   <td>？</td> 
   <td>零或者一个{ <span style="color:#ff9900;">匹配0次或1次--不能单独使用</span>}</td> 
  </tr> 
  <tr> 
   <td>*</td> 
   <td>零或者多个{ <span style="color:#ff9900;">匹配0次或多次 --不能单独使用</span>}</td> 
  </tr> 
  <tr> 
   <td>+</td> 
   <td>一个或者多个{ <span style="color:#ff9900;">匹配一次或多次--不能单独使用</span>}</td> 
  </tr> 
  <tr> 
   <td>{m}</td> 
   <td>正好出现m次,&nbsp;刚好匹配m次</td> 
  </tr> 
  <tr> 
   <td>{m,}</td> 
   <td>至少出现了m次,至少匹配m次</td> 
  </tr> 
  <tr> 
   <td>{m,n}</td> 
   <td>至少出现了m次，最多出现n次,匹配m-n次</td> 
  </tr> 
  <tr> 
   <td>+?</td> 
   <td>一个或者多个，非贪婪</td> 
  </tr> 
  <tr> 
   <td>??</td> 
   <td>零个或者一个，非贪婪</td> 
  </tr> 
  <tr> 
   <td>{m}?</td> 
   <td>和{m}一样</td> 
  </tr> 
  <tr> 
   <td>{m,}?</td> 
   <td>至少出现m次，非贪婪的，所以一旦达到了m次就会停止匹配</td> 
  </tr> 
  <tr> 
   <td>{m,n}?</td> 
   <td>至少出现m次，最多出现n次，非贪婪的，只要可能，就会只<br> 匹配m次</td> 
  </tr> 
 </tbody> 
</table>

### 2.3其他元字符 ###

<table style="width:500px;">  表三  
 <tbody> 
  <tr> 
   <td>|</td> 
   <td>左右两边表达式之间“或”关系，匹配左边或者右边</td> 
  </tr> 
  <tr> 
   <td>(...)</td> 
   <td>定义一个子表达式</td> 
  </tr> 
  <tr> 
   <td>\n</td> 
   <td>引用和子表达式第n次匹配的文本。向后引用可以是从\1到\9</td> 
  </tr> 
  <tr> 
   <td>\</td> 
   <td>如果后面没有跟着数字的话，\是个转义字符。比如，模式\\1就<br> 是一个反斜杠后面带这个数字1，\（就是一个开括号（而不再是<br> 个子表达式的开始)，等等</td> 
  </tr> 
  <tr> 
   <td>^</td> 
   <td>把一个表达式固定在字符串的开头部分（如果是多行模式则在行头）</td> 
  </tr> 
  <tr> 
   <td>$</td> 
   <td>把一个表达式固定在字符串的结尾（如果是多行模式，则在行尾）</td> 
  </tr> 
  <tr> 
   <td>\A</td> 
   <td>不过是否指定了多行模式，都把表达式固定在字符串的开头位置</td> 
  </tr> 
  <tr> 
   <td>\Z</td> 
   <td>不管是否指定了多行模式，都把表达式固定在字符串的末尾，或<br> 者是行尾的换行</td> 
  </tr> 
  <tr> 
   <td>\z</td> 
   <td>不过是否指定了多行模式，把表达式固定在字符串的末尾</td> 
  </tr> 
 </tbody> 
</table>

### 2.4支持的字符类 ###

<table style="width:500px;">  表四  
 <tbody> 
  <tr> 
   <td>[:alnum:]</td> 
   <td>字母和数字符号（等于[:alpha:]+[:digit:])</td> 
  </tr> 
  <tr> 
   <td>[:alpha:]</td> 
   <td>只是字母字符</td> 
  </tr> 
  <tr> 
   <td>[:blank:]</td> 
   <td>空白字符，比如空格和tab</td> 
  </tr> 
  <tr> 
   <td>[:cntrl:]</td> 
   <td>非打印字符，或者控制字符</td> 
  </tr> 
  <tr> 
   <td>[:digit:]</td> 
   <td>数字</td> 
  </tr> 
  <tr> 
   <td>[:graph:]</td> 
   <td>图形字符（等于[:punct:]+[:upper:]+[:lower:]+[:digit:])</td> 
  </tr> 
  <tr> 
   <td>[:lower:]</td> 
   <td>小写字母</td> 
  </tr> 
  <tr> 
   <td>[:print:]</td> 
   <td>可打印字符</td> 
  </tr> 
  <tr> 
   <td>[:punct:]</td> 
   <td>标点符号</td> 
  </tr> 
  <tr> 
   <td>[:space:]</td> 
   <td>空白字符，比如空格、跳页、换行、回车换行、水平制表符、垂直制表符</td> 
  </tr> 
  <tr> 
   <td>[:upper:]</td> 
   <td>大写字母</td> 
  </tr> 
  <tr> 
   <td>[:xdigit:]</td> 
   <td>十六进制字符</td> 
  </tr> 
 </tbody> 
</table>

> **备注：**
> 
> 几个括号的比较：
> 
> \[ \]:一对方括号，定义的一个匹配模式列表。  
> ( ):一对小括号，定义的是一个子表达式，可以用于向后引用。  
> \{m\}：一对花括号，里面是数字，或者是数字加逗号，或者数字加逗号加数字。代表的是数量词。
> 
> 本节四个表内容参考 张晓明翻译的Oracle PL/SQL 程序设计（第5版）书籍，如有想详细了解的可以查看此书。
> 
> ![bdc7f835bce74f129828751540bdcc15.png][]
> 
> 强烈推荐这本书，内容讲的很详细，全书近1100多页，涵盖了PL/SQL中非常多的知识，想要此书的可以关注私信我吧！

日常有一些字符也会被常用到，如下表所示：

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td style="width:87px;">a|b</td> 
   <td style="width:53px;">a或b</td> 
   <td style="width:359px;"> <p>这里的a|b相当于[ab],但是如果需要和别的通配符同时使用时,这种写法就很麻烦,比如姓名是SKM开头的员工信息：</p> <pre><code>select * from emp 
where regexp_like(ename,'^S|^K|^M') </code></pre> <p>和另一个：</p> <pre><code>select * from emp 
where regexp_like(ename,'^[SKM]')</code></pre> </td> 
  </tr> 
  <tr> 
   <td style="width:87px;">[abc]&nbsp;</td> 
   <td style="width:53px;">a或b或c</td> 
   <td style="width:359px;">[ ]相当于一位,有点像in的意思,in (a,b,c),只有当需要判断多个值的时候才会用到中括号'[]'.</td> 
  </tr> 
  <tr> 
   <td style="width:87px;">[a-z]</td> 
   <td style="width:53px;">小写字母</td> 
   <td style="width:359px;">[[:LOWER:]]</td> 
  </tr> 
  <tr> 
   <td style="width:87px;">[A-Z]&nbsp; &nbsp;</td> 
   <td style="width:53px;">大写字母</td> 
   <td style="width:359px;">[[:UPPER:]] &nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:87px;">[a-zA-Z]</td> 
   <td style="width:53px;">大小写字母</td> 
   <td style="width:359px;">[[:alpha:]] &nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:87px;">[0-9]</td> 
   <td style="width:53px;">数字</td> 
   <td style="width:359px;">[[:digit:]] &nbsp;</td> 
  </tr> 
  <tr> 
   <td style="width:87px;">[0-9a-zA-Z]</td> 
   <td style="width:53px;">字母和数字</td> 
   <td style="width:359px;">[[:alphanum:]]</td> 
  </tr> 
 </tbody> 
</table>

## 三、正则表达式函数 ##

<table style="width:500px;">  正则表达式参数  
 <tbody> 
  <tr> 
   <td>source_string</td> 
   <td>待搜索的字符串。</td> 
  </tr> 
  <tr> 
   <td>expression</td> 
   <td>一个用来描述要寻找文本的模式的正则表达式。</td> 
  </tr> 
  <tr> 
   <td>replace_string</td> 
   <td>查找替换中要替换成的文本。</td> 
  </tr> 
  <tr> 
   <td>position</td> 
   <td>源字符串source_string中搜索开始的字符位置。缺省是l。</td> 
  </tr> 
  <tr> 
   <td>occurrence</td> 
   <td>要定位的模式出现次数。缺省是1，也就是第一次匹配。</td> 
  </tr> 
  <tr> 
   <td>return_option</td> 
   <td>只有REGEXP_INSTR才合法，用来确定返回的是匹配文本的开始字符位置还是结束字符位置。缺省是0，也就是开始位置。1代表的就是返回结束位置。</td> 
  </tr> 
  <tr> 
   <td>match parameter</td> 
   <td>这是一个文字串，我们用这个串指定选项从而该表正则表达式匹配引擎的行为。</td> 
  </tr> 
  <tr> 
   <td>Subexpression(Oracle数据库llg才有)</td> 
   <td>是一个数字(0~9)表明要匹配哪一个子表达式。缺省是0说明不会使用子表达式：</td> 
  </tr> 
  <tr> 
   <td>i</td> 
   <td>搜索时<span style="color:#fe2c24;"><strong>不区分大小写</strong></span></td> 
  </tr> 
  <tr> 
   <td>c</td> 
   <td>搜索时<span style="color:#fe2c24;"><strong>区分大小写</strong></span>(<span style="color:#ff9900;">缺省时，是由NLS SORT设置确定搜索是否要区分大小写</span>。)</td> 
  </tr> 
  <tr> 
   <td><br> n<br><br> &nbsp;</td> 
   <td>句点可以匹配换行符。缺省时，句点不会匹配换行符。(<span style="color:#ff9900;">允许将.匹配任意新增字符,如果列中值中存在回车符换行了,加匹配参数n,可以将回车当做一个字符</span>)</td> 
  </tr> 
  <tr> 
   <td>m&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td> 
   <td>修改和^以及$两个元字符相关的行的定义。缺省时，行意味着整个目标字符串。不过，通过m选项，可以把关于行的定义从整个目标字符串，改成该字符串中的任何一行，现在这个行就是由换行符分开的了。（<span style="color:#ff9900;">简单理解为：m是多行模式,允许将原字符串作为多个字符串对待</span>）</td> 
  </tr> 
  <tr> 
   <td>x</td> 
   <td>扩展模式,<strong><span style="color:#fe2c24;">忽略正则表达式中的空白字符</span></strong></td> 
  </tr> 
 </tbody> 
</table>

我们可以按照任意顺序指定多个匹配参数。比如。“in”和“i”的意思是一样的。不过，如  
果你指定的选项相互冲突，比如“ic”,则最后一个选项，这个例子中是“c”,会优先使用。

### 3.1regexp\_like ###

语法：

REGEXP_LIKE(source_string,expression
                [,match_parameter])

即：**regexp\_like(列,匹配模式\[,匹配参数\])：**

用于测试一个字符串是否与指定的正则表达式匹配。它返回一个布尔值，如果匹配成功则返回TRUE，否则返回FALSE。

*下面举例运用：*

①查询emp表中S开头或J开头的员工信息:

select *
    from emp
    where regexp_like(ename,'^[SJ]');

②查询emp表中不是S或J开头的员工信息 :

select *
    from emp
    where regexp_like(ename,'^[^S^J]');

③查询名字以S或K结尾的员工信息:

select *
    from emp
    where regexp_like(ename,'[SR]$');

④查询名字不以S或K结尾的员工信息:

select *
    from emp
    where regexp_like(ename,'[^SK]$');

⑤查询名字是4位的员工姓名:

select *
    from emp
    where regexp_like(ename,'^....$');
    --或者
    select *
    from emp
    where regexp_like(ename,'^.{4}$');

下面的例子会用到person表，首先创表插入数据，脚本如下：

--创建person表：
    create table person (first_name varchar2(20),
                         last_name varchar2(20),
                         email varchar2(40),
                         zip varchar2(20));
    --插入数据：
    insert into PERSON (first_name, last_name, email, zip)
    values ('Steven', 'Chen', 'steven@hp.com', '123456');
    insert into PERSON (first_name, last_name, email, zip)
    values ('James', 'Li', 'jamesli@sum.com' || chr(10) || 'lijames@oracle.com', '1b3d5f');
    insert into PERSON (first_name, last_name, email, zip)
    values ('Tina', 'Zhang', 'chillaxzx@163.com', '2456hd');
    insert into PERSON (first_name, last_name, email, zip)
    values ('Tom', 'Feng', 'Tomfeng@126.com', 'a654e5');
    insert into PERSON (first_name, last_name, email, zip)
    values ('Jonson', 'zhao', 'Jonson@google.com', 'edjksk');
    insert into PERSON (first_name, last_name, email, zip)
    values ('Vines', 'Wu', 'Vines@162.com', '2djks4');
    commit;

⑥查询zip列非数字结尾的人:

select *
    from person
    where regexp_like(zip,'[^0-9]$');

⑦查询zip列纯数字的人:

select *
    from person
    where regexp_like(zip,'^[0-9]*$');

⑧查询zip列数字开头字母结尾（不区分大小写）:

select *
    from person
    where regexp_like(zip,'^[0-9].*[a-zA-Z]$');
    --or
    select *
    from person
    where regexp_like(zip,'^[0-9].*[a-z]$','i');

⑨查询email 以l 开头的人（多行模式）:

select *
    from person
    where regexp_like(email,'^l','m');

⑩查询email 包含“m任意字符l” 的人:

select *
    from person
    where regexp_like(email,'m.l','n');

### 3.2regexp\_replace ###

语法：

REGEXP_REPLACE(source_string,expression
                   [,replace_string
                   [,position [,occurrence
                   [,match_parameter]]]])

即：**regexp\_replace(str1,匹配模式\[,str2\[,数1\[,数2\[,匹配参数\]\]\]\])  
或regexp\_replace(str1,匹配模式,str2,\[数1\],\[数2\],\[匹配参数\])**

> **注释：**
> 
> str1:原字符串；  
> str2:替换成的字符 ，不写表示替换为空；  
> 数1:从数1位开始(不写默认为1) ；  
> 数2:第数2次符合匹配模式的字符串，不写默认全替换；  
> (注:这里如果不写那也不能写匹配参数，不然系统会认为匹配参数就是他的数2)  
> 连起来是:把str1中从第数1位开始，第数2次符合匹配模式的字符串替换成str2。

用于在一个字符串中使用正则表达式进行搜索和替换。它将一个字符串中的所有匹配项替换为指定的字符串。

*下面举例运用：*

①把zip列所有的数字替换成\*：

select regexp_replace(zip,'[0-9]','*')
    from person;

②把zip列所有的非数字替换成空：

select regexp_replace(zip,'[^0-9]')
    from person;

③把zip第一个数字替换成？:

select regexp_replace(zip,'[0-9]','?',1,1)
    from person;

④查询zip列所有的数字部分:

select regexp_replace(zip,'[^0-9]')
    from person;

⑤查询zip列所有的字母部分:

select regexp_replace(zip,'[^[:alpha:]]')
    from person;

⑥把emp表的ename中的A-M 替换成\*，N-Z替换成\#:

select regexp_replace(
           regexp_replace(ename,'[A-M]','*'),'[N-Z]','#')
    from emp;

### 3.3regexp\_instr ###

语法：

REGEXP_INSTR(source_string,expression
                 [,position [,occurrence
                 [,returnoption
                 [,match_parameter
                 [,subexpression]]]]])

即：**regexp\_instr(str,匹配模式\[,数1\[,数2\[,数3\[,匹配参数\]\]\]\])** 查找字符串位置

> **注释：**
> 
> 从第数1位开始找，找第数2次匹配的位置；  
> 数1 数2不写默认是1；  
> 数3不写默认从前往后找，数3为0表示从前往后找；  
> 数3为非0返回“匹配的字符串”的最后一位位置的下一个位置，找不到返回0。

*下面举例运用：*

①查找zip列第一个非数字字符的位置 \*\*\*\*：

select zip,regexp_instr(zip,'[0-9]',2,1,1)
    from person;

②查找zip列第二次出现“字母和数字的组合”的位置：

select zip,regexp_instr(zip,'[a-z][0-9]',1,2,0,'i')
    from person;

③查找email列“@”第一次出现的位置，标点符号第一次出现的位置"."第一次出现的位置：

select regexp_instr(email,'@'),
           regexp_instr(email,'[[:punct:]]'),
           regexp_instr(email,'[.]')
    from person;

### 3.4regexp\_substr ###

语法：

REGEXP_SUBSTR(source_string,expression
                  [,position [,occurrence
                  [,match_parameter
                  [,subexpression]]]])

即：**regexp\_substr(str,匹配模式\[,数1\[,数2\[,匹配参数\]\]\])** 截取字符串

> **注释：**  
> \--当截取长度超过列中值的长度会返回空；  
> 从数1位开始找，截取第数2次匹配的字符串；  
> 数1 数2不写默认是1。

用于从一个字符串中提取一个匹配的子字符串。它返回一个字符串值，表示匹配的子字符串。

*下面举例运用：*

①截取zip列第一个“数字＋字母”的组合：

select regexp_substr(zip,'[0-9][a-zA-Z]')
    from person;

②截取zip列第一个连续的字母串:

select zip,regexp_substr(zip,'[a-zA-Z]{2,}')
    from person;

### 3.5regexp\_count ###

语法：

REGEXP_COUNT (source_string,expression
                  [,position
                  [,match_parameter]])

即：**regexp\_count(str,匹配模式\[,数\[,匹配参数\]\])** \--11g  
计算字符串出现的次数,从第数位开始计算 。