注册 登录
> Oracle

Oracle中的正则表达式

系统 1826 0

Oracle 10g 数据库内建了符合 IEEE POSIX (Portable Operating System for Unix) 标准的正则表达式。熟练使用正则表达式，可以写出简洁，强大的 SQL 语句。

 

正则表达式有几个优点优于常见的 LIKE 操作符和 INSTR 、 SUBSTR 及 REPLACE 函数的。这些传统的 SQL 函数不便于进行模式匹配。只有 LIKE 操作符通过使用 % 和 _ 字符匹配，但 LIKE 不支持表达式的重复、复杂的更替、字符范围、字符列表和 POSIX 字符类等等。

 

元字符（ Meta Character ）：

Sql 代码

  1. ^             使表达式定位至一行的开头   
  2. $             使表达式定位至一行的末尾   
  3. *             匹配  0  次或更多次   
  4. ?             匹配  0  次或  1  次   
  5. +             匹配  1  次或更多次   
  6. {m}           正好匹配  m  次   
  7. {m,}          至少匹配  m  次   
  8. {m, n}        至少匹配  m  次但不超过  n  次   
  9. [:alpha:]     字母字符   
  10. [: lower :]     小写字母字符   
  11. [: upper :]     大写字母字符   
  12. [:digit:]     数字   
  13. [:alnum:]     字母数字字符   
  14. [: space :]     空白字符（禁止打印），如回车符、换行符、竖直制表符和换页符 [:punct:]     标点字符   
  15. [:cntrl:]     控制字符（禁止打印）   
  16. [:print:]     可打印字符  |  分隔替换选项，通常与分组操作符  ()  一起使用   
  17. ( )           将子表达式分组为一个替换单元、量词单元或后向引用单元   
  18. [char]        字符列表       

Oracle 10g 提供了四个 regexp function: REGEXP_LIKE , REGEXP_REPLACE , REGEXP_INSTR , REGEXP_SUBSTR 。

Sql 代码

  1. REGEXP_LIKE ：比较一个字符串是否与正则表达式匹配   
  2. (srcstr, pattern [, match_option])   
  3.   
  4. REGEXP_INSTR ：在字符串中查找正则表达式，并且返回匹配的位置   
  5. (srcstr, pattern [, position [, occurrence [, return_option [, match_option]]]])   
  6.   
  7. REGEXP_SUBSTR ：返回与正则表达式匹配的子字符串   
  8. (srcstr, pattern [, position [, occurrence [, match_option]]])    
  9.   
  10. REGEXP_REPLACE ：搜索并且替换匹配的正则表达式   
  11. (srcstr, pattern [, replacestr [, position [, occurrence [, match_option]]]])  

REGEXP_LIKE ：比较一个字符串是否与正则表达式匹配

(srcstr, pattern [, match_option])

 

REGEXP_INSTR ：在字符串中查找正则表达式，并且返回匹配的位置

(srcstr, pattern [, position [, occurrence [, return_option [, match_option]]]])

 

REGEXP_SUBSTR ：返回与正则表达式匹配的子字符串

(srcstr, pattern [, position [, occurrence [, match_option]]])

 

REGEXP_REPLACE ：搜索并且替换匹配的正则表达式

(srcstr, pattern [, replacestr [, position [, occurrence [, match_option]]]])

 

其中各参数的含义为 :

Sql 代码

  1. srcstr:         被查找的字符数据。     
  2. pattern:        正则表达式。   
  3. occurrence:     出现的次数。默认为 1 。   
  4. position:       开始位置   
  5. return_option:  默认值为 0 ，返回该模式的起始位置；值为 1 则返回符合匹配条件的下一个字符的起始位置。   
  6. replacestr:     用来替换匹配模式的字符串。   
  7. match_option:   匹配方式选项。缺省为 c 。   
  8.                c ： case  sensitive   
  9.                I ： case  insensitive  
  10.                n ： (.) 匹配任何字符（包括 newline)   
  11.                m ：字符串存在换行的时候被作为多行处理       

下面通过一些具体的例子来说明如何使用这四个函数。首先创建一个测试数据表，

Sql 代码

  1. SQL> create table person (   
  2.   2  first_name varchar(20),   
  3.   3  last_name varchar(20),   
  4.   4  email varchar(100),   
  5.   5  zip varchar(6));   
  6.   
  7. Table created.   
  8.   
  9. SQL> insert into person values ('Steven', 'Chen', ' steven@hp.com ', '123456');   
  10. 1 row created.   
  11.   
  12. SQL> insert into person values ('James', 'Li', ' jamesli@sun.com ' || chr(10) || ' lijames@oracle.com ', '1b3d5f');   
  13. 1 row created.   
  14.   
  15. SQL> commit;   
  16. Commit complete.   
  17.   
  18. SQL> select * from person;   
  19.   
  20. FIRST_NAME LAST_NAME  EMAIL                ZIP   
  21. ---------- ---------- -------------------- ------   
  22. Steven     Chen        steven@hp.com         123456   
  23. James      Li          jamesli@sun.com       1b3d5f   
  24.                        lijames@oracle.com   

SQL> create table person (

  2  first_name varchar(20),

  3  last_name varchar(20),

  4  email varchar(100),

  5  zip varchar(6));

 

Table created.

 

SQL> insert into person values ('Steven', 'Chen', ' steven@hp.com ', '123456');

1 row created.

 

SQL> insert into person values ('James', 'Li', ' jamesli@sun.com ' || chr(10) || ' lijames@oracle.com ', '1b3d5f');

1 row created.

 

SQL> commit;

Commit complete.

 

SQL> select * from person;

 

FIRST_NAME LAST_NAME  EMAIL                ZIP

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

Steven     Chen       steven@hp.com         123456

James      Li         jamesli@sun.com       1b3d5f

                      lijames@oracle.com

 

1 。 REGEXP_LIKE

Sql 代码

  1. SQL> select zip as invalid_zip from person where regexp_like(zip, '[^[:digit:]]');   
  2. INVALID_ZIP   
  3. --------------------   
  4. 1b3d5f   
  5.   
  6. SQL> select first_name from person where regexp_like(first_name, '^S.*n$');   
  7. FIRST_NAME   
  8. ----------   
  9. Steven   
  10.   
  11. SQL> select first_name from person where regexp_like(first_name, '^s.*n$');   
  12. no rows selected   
  13.   
  14. SQL> select first_name from person where regexp_like(first_name, '^s.*n$', 'c');   
  15. no rows selected   
  16.   
  17. SQL> select first_name from person where regexp_like(first_name, '^s.*n$', 'i');   
  18. FIRST_NAME   
  19. ----------   
  20. Steven   
  21.   
  22. SQL> select email from person where regexp_like(email, '^james.*com$');   
  23. no rows selected   
  24.   
  25. SQL> select email from person where regexp_like(email, '^james.*com$', 'n');   
  26. EMAIL   
  27. --------------------   
  28. jamesli@sun.com   
  29. lijames@oracle.com   
  30.   
  31. SQL> select email from person where regexp_like(email, '^li.*com$');   
  32. no rows selected   
  33.   
  34. SQL> select email from person where regexp_like(email, '^li.*com$', 'm');   
  35. EMAIL   
  36. --------------------   
  37. jamesli@sun.com   
  38. lijames@oracle.com   

SQL> select zip as invalid_zip from person where regexp_like(zip, '[^[:digit:]]');

INVALID_ZIP

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

1b3d5f

 

SQL> select first_name from person where regexp_like(first_name, '^S.*n$');

FIRST_NAME

----------

Steven

 

SQL> select first_name from person where regexp_like(first_name, '^s.*n$');

no rows selected

 

SQL> select first_name from person where regexp_like(first_name, '^s.*n$', 'c');

no rows selected

 

SQL> select first_name from person where regexp_like(first_name, '^s.*n$', 'i');

FIRST_NAME

----------

Steven

 

SQL> select email from person where regexp_like(email, '^james.*com$');

no rows selected

 

SQL> select email from person where regexp_like(email, '^james.*com$', 'n');

EMAIL

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

jamesli@sun.com

lijames@oracle.com

 

SQL> select email from person where regexp_like(email, '^li.*com$');

no rows selected

 

SQL> select email from person where regexp_like(email, '^li.*com$', 'm');

EMAIL

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

jamesli@sun.com

lijames@oracle.com

 

注意上面分别测试了不同的 match_option 对结果的影响。

 

2 。 REGEXP_INSTR

Sql 代码

  1. 查找 zip 中第一个非数字字符的位置   
  2. SQL> select regexp_instr(zip, '[^[:digit:]]') as position from person;   
  3.   POSITION   
  4. ----------   
  5.          0   
  6.          2   
  7.   
  8. 从第三个字符开始，查找 zip 中第二个非数字字符的位置   
  9. SQL> select regexp_instr(zip, '[^[:digit:]]', 3, 2) as position from person;   
  10.   POSITION   
  11. ----------   
  12.          0   
  13.          6   
  14.   
  15. 从第三个字符开始，查找 zip 中第二个非数字字符的下一个字符位置   
  16. SQL> select regexp_instr(zip, '[^[:digit:]]', 3, 2, 1) as position from person;   
  17.   POSITION   
  18. ----------   
  19.          0   
  20.          7  

查找 zip 中第一个非数字字符的位置

SQL> select regexp_instr(zip, '[^[:digit:]]') as position from person;

  POSITION

----------

         0

         2

 

从第三个字符开始，查找 zip 中第二个非数字字符的位置

SQL> select regexp_instr(zip, '[^[:digit:]]', 3, 2) as position from person;

  POSITION

----------

         0

         6

 

从第三个字符开始，查找 zip 中第二个非数字字符的下一个字符位置

SQL> select regexp_instr(zip, '[^[:digit:]]', 3, 2, 1) as position from person;

  POSITION

----------

         0

         7

 

3 。 REGEXP_SUBSTR

Sql 代码

  1. SQL> select regexp_substr(zip, '[^[:digit:]]') as zip from person;   
  2. ZIP   
  3. ------------------   
  4. b   
  5.   
  6. SQL> select regexp_substr(zip, '[^[:digit:]]', 3, 2) as zip from person;   
  7. ZIP   
  8. ------------   
  9. f  

SQL> select regexp_substr(zip, '[^[:digit:]]') as zip from person;

ZIP

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

b

 

SQL> select regexp_substr(zip, '[^[:digit:]]', 3, 2) as zip from person;

ZIP

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

f

 

4 。 REGEXP_REPLACE

Sql 代码

  1. 把 zip 中所有非数字字符替换为 0   
  2. SQL> update person set zip=regexp_replace(zip, '[^[:digit:]]', '0')   
  3.   2  where regexp_like(zip, '[^[:digit:]]');   
  4. 1 row updated.   
  5.   
  6. SQL> select zip from person;   
  7. ZIP   
  8. ------   
  9. 123456   
  10. 103050  

把 zip 中所有非数字字符替换为 0

SQL> update person set zip=regexp_replace(zip, '[^[:digit:]]', '0')

  2  where regexp_like(zip, '[^[:digit:]]');

1 row updated.

 

SQL> select zip from person;

ZIP

------

123456

103050

 

后向引用（ backreference ）：

 

后向引用是 一个很有用的特性。它能够把子表达式的匹配部分保存在临时缓冲区中，供以后重用 。缓冲区从左至右进行编号，并利用 \digit 符号进行访问。子表达式用一组圆括号来显示。利用后向引用可以实现较复杂的替换功能。

Sql 代码

  1. SQL> select regexp_replace('Steven Chen', '(.*) (.*)', '\2, \1') as reversed_name from dual;   
  2.   
  3. REVERSED_NAME   
  4. --------------------   
  5. Chen, Steven  

SQL> select regexp_replace('Steven Chen', '(.*) (.*)', '\2, \1') as reversed_name from dual;

 

REVERSED_NAME

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

Chen, Steven

 

在 DDL 中也可以正则表达式，比如 Constraint, index, view

Sql 代码

  1. SQL> alter table person add constraint constraint_zip check (regexp_like(zip, '^[[:digit:]]+$'));   
  2.   
  3. SQL> create index person_idx on person(regexp_substr(last_name, '^[[:upper:]]'));  

一. 匹配字符   

字符类

匹配的字符

举 例

d

从０ - ９的任一数字

dd 匹配 72, 但不匹配 aa 或 7a

D

任一非数字字符

DDD 匹配 abc, 但不匹配 123

w

任一单词字符，包括 A-Z,a-z,0-9 和下划线

wwww 匹配 Ab-2 ，但不匹配 ∑ £$%* 或 Ab_ @

W

任一非单词字符

W 匹配＠，但不匹配 a

s

任一空白字符，包括制表符，换行符，回车符，换页符和垂直制表符

匹配在 HTML,XML 和 其他 标准定义中的所有传统空白字符

S

任一非空白字符

空白字符以外的任意字符 , 如 A% &g3; 等

.

任一字符

匹配除换行符以外的任意字符除非设置了 MultiLine 先项

[…]

括号中的任一字符

[abc] 将匹配一个单字符 ,a,b 或 c.

[a-z] 将匹配从 a 到 z 的任一字符

[^…]

不在括号中的任一字符

[^abc] 将匹配一个 a 、 b 、 c 之外的单字符 , 可以 a,b 或 A 、 B 、 C

[a-z] 将匹配不属于 a-z 的任一字符 , 但可以匹配所有的大写字母

                   

二. 重复字符

重复字符

含 义

举 例

｛ n ｝

匹配前面的字符 n 次

x{2} 匹配 xx, 但不匹配 x 或 xxx

｛ n, ｝

匹配前面的字符至少 n 次

x{2} 匹配 2 个或更多的 x, 如 xxx,xxx. .

｛ n,m ｝

匹配前面的字符至少 n 次 , 至多 m 次。如果 n 为 0 ，此参数为可选参数

x{2,4} 匹配 xx,xxx,xxxx, 但不匹配 xxxxx

?

匹配前面的字符 0 次或 1 次，实质上也是可选的

x? 匹配 x 或零个 x

+

匹配前面的字符 0 次或多次

x+ 匹配 x 或 xx 或大于 0 的任意多个 x

*

匹配前面的字符 0 次或更多次

x* 匹配 0,1 或更多个 x

三 . 定位字符

定位字符

描 述

^

随后的模式必须位于字符串的开始位置，如果是一个多行字符串，则必须位于行首。对于多行文本（包含回车符的一个字符串）来说，需要设置多行标志

$

前面的模式必须位于字符串的未端，如果是一个多行字符串，必须位于行尾

A

前面的模式必须位于字符串的开始位置，忽略多行标志

z

前面的模式必须位于字符串的未端，忽略多行标志

Z

前面的模式必须位于字符串的未端，或者位于一个换行符前

b

匹配一个单词边界，也就是一个单词字符和非单词字符中间的点。要记住一个单词字符是 [a-zA-Z0-9] 中的一个字符。位于一个单词的词首

B

匹配一个非单词字符边界位置，不是一个单词的词首

分组字符

分组字符

定 义

举 例

（）

此字符可以组合括号内模式所匹配的字符，它是一个捕获组，也就是说模式匹配的字符作为最终设置了 ExplicitCapture 选项 ―― 默认状态下字符不是匹配的一部分

输入字符串为： ABC1DEF2X Y

匹配 3 个从 A 到 Z 的字符和 1 个数字的正则表达式：（ [A-Z]{3}d ）

将产生两次匹配： Match 1=ABC1;Match 2=DEF2

每次匹配对应一个组： Match1 的第一个组＝ ABC;Match2 的第 1 个组＝ DEF

有了反向引用，就可以通过它在正则表达式中的编号以及 C# 和类 Group,GroupCollection 来访问组。如果设置了 ExplicitCapture 选项，就不能使用组所捕获的内容

（ ?: ）

此字符可以组合括号内模式所匹配的字符，它是一个非捕获组，这意味着模式所的字符将不作为一个组来捕获，但它构成了最终匹配结果的一部分。它基本上与上面的组类型相同，但设定了选项 ExplicitCapture

输入字符串为： 1A BB SA1 C

匹配一个数字或一个 A 到 Z 的字母，接着是任意单词字符的正则表达式为：（ ?:d|[A-Z]w ）

它将产生 3 次匹配：每 1 次匹配＝ 1A ；每 2 次匹配＝ BB; 每 3 次匹配＝ SA

但是没有组被捕获

（ ?<name> ）

此选项组合括号内模式所匹配的字符，并用尖括号中指定的值为组命名。在正则表达式中，可以使用名称进行反向引用，而不必使用编号。即使不设置 ExplicitCapture 选项，它也是一个捕获组。这意味着反向引用可以利用组内匹配的字符，或者通过 Group 类访问

输入字符串为： Characters in Sienfeld included Jerry Seinfeld,Elaine Benes,Cosno Kramer and George Costanza 能够匹配它们的姓名，并在一个组 llastName 中捕获姓的正则表达式为： b[A-Z][a-z]+(?<lastName>[A-Z][a-z]+)b

它产生了 4 次匹配： First Match=Jerry Seinfeld; Second Match=Elaine Benes; Third Match=Cosmo Kramer; Fourth Match=George Costanza

每一次匹配都对应了一个 lastName 组：

第 1 次匹配： lastName group=Seinfeld

第 2 次匹配： lastName group=Benes

第 3 次匹配： lastName group=Kramer

第 4 次匹配： lastName group=Costanza

不管是否设置了选项 ExplictCapture ，组都将被捕获

（ ?= ）

正声明。声明的右侧必须是括号中指定的模式。此模式不构成最终匹配的一部分

正则表达式 S+(?=.NET) 要匹配的输入字符串为： The languages were Java,C#.NET,VB.NET,C,Jscript.NET,Pascal

将产生如下匹配： 〕

C#

VB

JScript.

（ ?! ）

负声明。它规定模式不能紧临着声明的右侧。此模式不构成最终匹配的一部分

d{3}(?![A-Z]) 要匹配的输入字符串为： 123A 456 789111C

将产生如下匹配：

456

789

（ ? <= ）

反向正声明。声明的左侧必须为括号内的指定模式。此模式不构成最终匹配的一部分

正则表达式 (?<=New)([A-Z][a-z]+) 要匹配的输入字符串为： The following states,New Mexico,West Virginia,Washington,New England

它将产生如下匹配：

Mexico

England

（ ? <! ）

反向正声明。声明的左侧必须不能是括号内的指定模式。此模式不构成最终匹配的一部分

正则表达式 (?<!1)d{2}([A-Z]) 要匹配的输入字符串如下： 123A456F789C111A

它将实现如下匹配：

56F

89C

（ ?> ）

非回溯组。防止 Regex 引擎回溯并且防止实现一次匹配

假设要匹配所有以 “ ing” 结尾的单词。输入字符串如下： He was very trusin g

正则表达式为： .*ing

它将实现一次匹配 ―― 单词 trusting 。 “ .” 匹配任意字符，当然也匹配 “ ing” 。所以， Regex 引擎回溯一位并在第 2 个 “t” 停止，然后匹配指定的模式 “ ing” 。但是，如果禁用回溯操作： (? >.*)ing

它将实现 0 次匹配。 “.” 能匹配所有的字符，包括 “ ing”―― 不能匹配，从而匹配失败

决策字符

字 符

描 述

举 例

（ ?(regex)yes_regex|no_regex ）

如果表达式 regex 匹配，那么将试图匹配表达式 yes 。否则匹配表达式 no 。正则表达式 no 是可先参数。注意，作出决策的模式宽度为 0. 这意味着表达式 yes 或 no 将从与 rege x 表达式相同的位置开始匹配

正则表达式 (?(d)dA|A-Z)B) 要匹配的输入字符串为： 1A CB3A5C 3B

它实现的匹配是：

1A

CB

3A

（ ?(group name or number)yes_regex|no_regex ）

如果组中的正则表达式实现了匹配，那么试图匹配 yes 正则表达式。否则，试图匹配正则表达式 no 。 no 是可先的参数

正则表达式

(d7)?-(?(1)dd[A-Z]|[A-Z][A-Z] 要匹配的输入字符串为：

77-77A 69-AA 57-B

它实现的匹配为：

77-77A

－ AA

替换字符

字 符

描 述

$group

用 group 指定的组号替换

${name}

替换被一个 (? <name>) 组匹配的最后子串

$$

替换一个字符 $

$&

替换整个的匹配

$^

替换输入字符串匹配之前的所有文本

$’

替换输入字符串匹配之后的所有文本

$+

替换最后捕获的组

$_

替换整个的输入字符串

转义序列

字 符

描 述

 

匹配字符 “ ”

.

匹配字符 “. ”

*

匹配字符 “ *”

+

匹配字符 “ +”

?

匹配字符 “? ”

|

匹配字符 “| ”

(

匹配字符 “ (”

)

匹配字符 “ )”

{

匹配字符 “{ ”

}

匹配字符 “} ”

^

匹配字符 “^ ”

$

匹配字符 “$ ”

n

匹配换行符

r

匹配回车符

t

匹配制表符

v

匹配垂直制表符

f

匹配换面符

nnn

匹配一个 8 进数字， nnn 指定的 ASCII 字符。如 103 匹配大写的 C

xnn

匹配一个 16 进数字， nn 指定的 ASCII 字符。如 x43 匹配大写的 C

unnnn

匹配由 4 位 16 进数字（由 nnnn 表示）指定的 Unicode 字符

cV

匹配一个控制字符，如 cV 匹配 Ctrl- V

选项标志

选项标志

名 称

I

IgnoreCase

M

Multiline

N

ExplicitCapture

S

SingleLine

X

IgnorePatternWhitespace

选项本身的信作含义如下表所示：

标 志

名 称

IgnoreCase

使模式匹配不区分大小写。默认的选项是匹配区分大小写

RightToLeft

从右到左搜索输入字符串。默认是从左到右以符合英语等的阅读习惯，但不符合阿拉伯语或希伯来语的阅读习惯

None

不设置标志。这是默认选项

Multiline

指定 ^ 和 $ 可以匹配行首和行尾，以及字符串的开始和结尾。这意味着可以匹配每个用换行符分隔的行。但是，字符 “.” 仍然不匹配换行符

SingleLine

规定特殊字符 “.” 匹配任意的字符，包括换行符。默认情况下，特殊字符 “.” 不匹配换行符。通常与 MultiLine 选项一起使用

ECMAScript.

ECMA(European Coputer Manufacturer’s Association, 欧洲计算机生产商协会 ) 已经定义了正则表达式应该如何实现，而且已经在 ECMAScript 规范中实现，这是一个基于标准的 JavaScript 。这个选项只能与 IgnoreCase 和 MultiLine 标志一起使用。与其它任何标志一起使用， ECMAScript 都将产生异常

IgnorePatternWhitespace

此选项从使用的正则表达式模式中删除所有非转义空白字符。它使表达式能跨越多行文本，但必须确保对模式中所有的空白进行转义。如果设置了此选项，还可以使用 “#” 字符来注释下则表达式

Complied

它把正则表达式编译为更接近机器代码的代码。这样速度快，但不允许对它进行任何修改

Oracle中的正则表达式


更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描上面二维码支持博主2元、5元、10元、自定义金额等您想捐的金额吧，站长会非常 感谢您的哦！！！

发表我的评论
最新评论 总共0条评论