Oracle中的正则表达式

系统 1870 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

从0 - 9的任一数字

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条评论