Oracle正则表达式及测试

Oracle 中的支出正则表达式的函数主要有下面四个：

1、REGEXP_LIKE:与LIKE的功能相似

2、REGEXP_INSTR:与INSTR的功能相似

3、REGEXP_SUBSTR:与SUBSTR的功能相似

4、REGEXP_REPLACE:与REPLACE的功能相似

它们在用法上与Oracle SQL函数LIKE/INSTR/SUBSTR和REPLACE用法相同，但是它们使用POSIX正则表达式代替了老的百分号(%)和通配符(_)字符。

POSIX正则表达式由标准的元字符，(metacharacters)所构成：

'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。

'$ '匹配输入字符的结尾位置。如果设置了REGEXP对象的multiline属性，则$也匹配'\n'或'\r'。

'.' 匹配除换行符之外的任何单字符。

'?' 匹配前面的子表达式零次或一次。

'+' 匹配前面的子表达式一次或多次。

'*' 匹配前面的子表达式零次或多次。

'|' 指明两项之间的一个选择。例子'([a-z]+[0-9]+)$'表示所有小写字母或数字组合成的字符串。

'()' 标记一个子表达式的开始和结束位置。

'[]' 标记一个中括号表达式。

'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n,'{m}'表示出现m次，'{m,}'表示至少出现m次。

\num 匹配num，其中num是一个正整数。对所获取的匹配的引用。

字符簇：

[[:alpha:]] 任何字母。

[[:digit:]] 任何数字

[[:alnum:]] 任何字母和数字

[[:space:]] 任何白字符。

[[:upper:]]任何大写字母

[[:lower:]] 任何小写字母

[[:punct:]] 任何标点符号。

[[:xdigit:]]任何16进制的数字，相当于[0-9a-fA-F]。

各种操作符的运算优先级

\转义符

(),(?:),(?=),[] 圆括号和方括号

*,+,?,{n},{n,},{n,m}限定符

^,$,anymetacharacter 位置和顺序

搭建测试环境11g，windows7 、SQL Plus,在scott用户下

regexp_like函数

--创建表

create table fzq(

id varchar(4),

value varchar(10)

);

--数据插入

insert into fzq values('1','1234560');

insert into fzq values('2','1234560');

insert into fzq values('3','1b3b560');

insert into fzq values('4','abc');

insert into fzq values('5','abcde');

insert into fzq values('6','ADREasx');

insert into fzq values('7','123 45');

insert into fzq values('8','adc de');

insert into fzq values('9','adc,.de');

insert into fzq values('10','1B');

insert into fzq values('10','abcbvbnb');

insert into fzq values('11','11114560');

insert into fzq values('11','11124560');

--查询value中以1开头60结束的记录并且长度是7位

方法一：

SQL> select * from fzq where value like '1____60';

ID       VALUE
-------- --------------------
2        1234560
1        1234560
3        1b3b560

方法二：

SQL> select * from fzq where regexp_like(value,'1....60') and length(value) =7;

ID       VALUE
-------- --------------------
2        1234560
1        1234560
3        1b3b560

SQL>

--查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。

--使用like就不是很好实现了。

方法一：

SQL> select * from fzq where regexp_like(value,'1[0-9]{4}60');

ID       VALUE
-------- --------------------
2        1234560
1        1234560
11       11114560
11       11124560

SQL> select * from fzq where regexp_like(value,'1[0-9]{4}60') and length(value) =7;

ID       VALUE
-------- --------------------
2        1234560
1        1234560

SQL>

因为正则表达式中没有表示长度的，所以只能用后面的这个查询。

方法二：也可以使用字符簇

SQL> select * from fzq where regexp_like(value,'1[[:digit:]]{4}60') and length(value)=7;

ID       VALUE
-------- --------------------
2        1234560
1        1234560

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

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

ID       VALUE
-------- --------------------
3        1b3b560
4        abc
5        abcde
6        ADREasx
7        123 45
8        abc de
9        adc,.de
10       1B

已选择8行。

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

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

ID       VALUE
-------- --------------------
4        abc
5        abcde
6        ADREasx
8        abc de
9        adc,.de

SQL>

--查询以12或者1b开头的记录.不区分大小写。

SQL> select * from fzq where regexp_like(value,'^1[2b]','i');

ID       VALUE
-------- --------------------
2        1234560
1        1234560
3        1b3b560
7        123 45
10       1B

SQL>

---查询以12或者1b开头的记录.区分大小写。

SQL> select * from fzq where regexp_like(value,'^1[2b]');

ID       VALUE
-------- --------------------
2        1234560
1        1234560
3        1b3b560
7        123 45

--

--查询数据中包含空白的记录。

SQL> select * from fzq where regexp_like(value,'[[:space:]]');

ID       VALUE
-------- --------------------
7        123 45
8        abc de

SQL>

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

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

ID       VALUE
-------- --------------------
2        1234560
1        1234560
4        abc
5        abcde
11       11114560
11       11124560

已选择6行。

--查询任何包含标点符号的记录。

SQL> select * from fzq where regexp_like(value,'[[:punct:]]');

ID       VALUE
-------- --------------------
9        adc,.de

SQL>

regexp_replace函数

--所有连续两个以上的空白改成逗号 

SQL> select regexp_replace('坐火车 去 西藏 玩 看 秀丽山川','\s+',',') 逗号隔开 from dual;

逗号隔开
--------------------------------------------------------------------------------
坐火车,去,西藏,玩,看,秀丽山川

regexp_instr函数

regexp_instr 是Oracle正则表达式中的一个函数。这个函数其实与instr函数其实相似。

regexp_instr的原型如下：

regexp_instr(x,pattern[,start[,occurrence[,return_option[,match_option]]]])

这里每个参数分别含义如下：

x待匹配的字符串

pattern 待匹配的模式

start 开始匹配的位置，如果不指定默认为1

occurrence 匹配的次数，如果不指定默认为1

return_option 指定返回值的类型，如果该参数为0，则返回值为匹配位置的第一个字符，

如果该值为非0则返回匹配值的最后一个位置。

match_option 可以用这个参数来修改一些默认的配置设置。这个值与前面所说的regexp_like函数中的match_option参数的意义是意义的。

上例子：

SQL> select instr('avdsdabasdab','a',1,1) from dual;

INSTR('AVDSDABASDAB','A',1,1)
-----------------------------
                            1

SQL>

同：

SQL> select regexp_instr('avdsdabasdab','a',1,1) from dual;

REGEXP_INSTR('AVDSDABASDAB','A',1,1)
------------------------------------
                                   1

SQL>

--从给定自动的第一个字符开始查找a字串，返回查找到第一个a的位置

SQL> select instr('avdsdabasdab','a',1,2) from dual;

INSTR('AVDSDABASDAB','A',1,2)
-----------------------------
                            6

SQL>

--从给定字段的第一个字符开始查找a字串，返回查找到第二个a的位置

SQL> select instr('avdsdabasdab','a',6,1) from dual;

INSTR('AVDSDABASDAB','A',6,1)
-----------------------------
                            6

SQL>

---从给定字段的第六个字符开始查找a字串，返回查找到第一个a的位置

regexp_substr函数

5个参数

第一个是输入的字符串

第二个是正则表达式

第三个标识从第几个字符开始正则表达式匹配。(默认为1)

第四个是标识第几个匹配组。(默认为1)

第五个是取值范围：

i:大小写不敏感；

c:大小写敏感

n:点号 . 不匹配换行符号；

m:多行模式；

x:扩展模式，忽略正则表达式中的空白字符

全部测试数据

--创建表

create table test_reg_substr(

A varchar(50)

);

--数据插入

insert into test_reg_substr values('ABC123XYZ');

insert into test_reg_substr values('ABC123XYZ456');

--查询全部测试数据

SQL> select * from test_reg_substr;

A
--------------------------------------------------------
ABC123XYZ
ABC123XYZ456

SQL>

--检索中间的数字

SQL> select regexp_substr(a,'[0-9]+') from test_reg_substr
  2  where regexp_like(a,'[0-9]+') ;

REGEXP_SUBSTR(A,'[0-9]+')
----------------------------------------------------------------
123
123

SQL>

--检索中间的数字(从第一个字母开始匹配，找第2个匹配项目)

SQL> select nvl(regexp_substr(a,'[0-9]+',1,2),'-') as a from test_reg_substr
  2  where regexp_like(a,'[0-9]+');

A
--------------------------------------------------------------------------------
456

--取得“字符集合”

SQL> select regexp_substr(a,'\w+') from test_reg_substr
  2  where regexp_like(a,'\w+');

REGEXP_SUBSTR(A,'\W+')
-------------------------------------------------------------------
ABC123XYZ
ABC123XYZ456

--取得“字符集合”(从第一个字母开始匹配，找第2个匹配项目)

SQL> select nvl(regexp_substr(a,'\w+',1,2),'-') as a from test_reg_substr
  2  where regexp_like(a,'\w+');

A
--------------------------------------------------------------------------------
-
-

SQL>