Oracle正则表达式函数.docx
《Oracle正则表达式函数.docx》由会员分享，可在线阅读，更多相关《Oracle正则表达式函数.docx（10页珍藏版）》请在冰豆网上搜索。
Oracle正则表达式函数
Oracle正则表达式函数
Oracle使用正则表达式离不开这4个函数：
1。
regexp_like
2。
regexp_substr
3。
regexp_instr
4。
regexp_replace
看函数名称大概就能猜到有什么用了。
regexp_like只能用于条件表达式，和like类似，但是使用的正则表达式进行匹配，语法很简单：
regexp_substr函数，和substr类似，用于拾取合符正则表达式描述的字符子串，语法如下：
regexp_instr函数，和instr类似，用于标定符合正则表达式的字符子串的开始位置，语法如下：
regexp_replace函数，和replace类似，用于替换符合正则表达式的字符串，语法如下：
这里解析一下几个参数的含义：
1。
source_char，输入的字符串，可以是列名或者字符串常量、变量。
2。
pattern，正则表达式。
3。
match_parameter，匹配选项。
取值范围：
i：
大小写不敏感；c：
大小写敏感；n：
点号.不匹配换行符号；m：
多行模式；x：
扩展模式，忽略正则表达式中的空白字符。
4。
position，标识从第几个字符开始正则表达式匹配。
5。
occurrence，标识第几个匹配组。
6。
replace_string，替换的字符串。
说了一堆文绉绉的，现在开始实例演练了，在此之前先建好一个表。
01createtabletmpas
02withdataas（
03 select'like'asid,'a9999'asstrfromdualunionall
04 select'like' ,'a9c' fromdualunionall
05 select'like' ,'A7007' fromdualunionall
06 select'like' ,'123a34cc' fromdualunionall
07 select'substr' ,'123,234,345' fromdualunionall
08 select'substr' ,'12,34.56:
78' fromdualunionall
09 select'substr' ,'123456789' fromdualunionall
10 select'instr' ,'192.168.0.1' fromdualunionall
11 select'replace' ,'（020）12345678'fromdualunionall
12 select'replace' ,'001517729C28'fromdual
13）
14select*fromdata;
15
16select*fromtmp;
17ID STR
18--------------------
19like a9999
20like a9c
21like A7007
22like 123a34cc
23substr 123,234,345
24substr 12,34.56:
78
25substr 123456789
26instr 192.168.0.1
27replace（020）12345678
28replace001517729C28
regexp_like例子：
01selectstrfromtmpwhereid='like'andregexp_like（str,'A\d+','i'）;--'i'忽略大小写
02STR
03-------------
04a9999
05a9c
06A7007
07123a3
4cc
08
09selectstrfromtmpwhereid='like'andregexp_like（str,'a\d+'）;
10STR
11-------------
12a9999
13a9c
14123a34cc
15
16selectstrfromtmpwhereid='like'andregexp_like（str,'^a\d+'）;
17STR
18-------------
19a9999
20a9c
21
22selectstrfromtmpwhereid='like'andregexp_like（str,'^a\d+$'）;
23STR
24-------------
25a9999
regexp_substr例子：
01colstrformata15;
02select
03 str,
04 regexp_substr（str,'[^,]+'） str,
05 regexp_substr（str,'[^,]+',1,1）str,
06 regexp_substr（str,'[^,]+',1,2）str, --occurrence第几个匹配组
07 regexp_substr（str,'[^,]+',2,1）str --position从第几个字符开始匹配
08fromtmp
09whereid='substr';
10STR STR STR STR STR
11---------------------------------------------------------------------------
12123,234,345 123 123 234 23
1312,34.56:
78 12 12 34.56:
78 2
14123456789 123456789 123456789 23456789
15
16select
17 str,
18 regexp_substr（str,'\d'） str,
19 regexp_substr（str,'\d+' ,1,1）str,
20 regexp_substr（str,'\d{2}',1,2）str,
21 regexp_substr（str,'\d{3}',2,1）str
22fromtmp
23whereid='substr';
24STR STR STR STR STR
25---------------------------------------------------------------------------
26123,234,345 1 123 23 234
2712,34.56:
78 1 12 34
28123456789 1 123456789 34 234
29
30
31selectregexp_substr（'123456789','\d',1,level）str --取出每位数字，有时这也是行转列的方式
32fromdual
33connectbylevel<=9
34STR
35---------------
361
372
383
394
405
416
427
438
449
regex_instr例子：
01colindformat9999;
02select
03 str,
04 regexp_instr（str,'\.' ）ind,
05 regexp_instr（str,'\.',1,2）ind,
06 regexp_instr（str,'\.',5,2）ind
07fromtmpwhereid='instr';
08STR IND IND IND
09------------------------------
10192.168.0.1 4 8 10
11
12select
13 regexp_instr（'192.168.0.1','\.',1,level）ind, --点号.所在的位置
14 regexp_instr（'192.168.0.1','\d',1,level）ind --每个数字的位置
15fromdual
16connectbylevel<= 9
17 IND IND
18----------
19 4 1
20 8 2
21 10 3
22 0 5
23 0 6
24 0 7
25 0 9
26 0 11
27 0 0
regex_replace例子：
01select
02 str,
03 regexp_replace（str,'020','GZ'）str,
04 regexp_replace（str,'（\d{3}）（\d{3}）','<\2\1>'）str--将第一、第二捕获组交换位置，用尖括号标识出来
05fromtmp
06whereid='replace';
07STR STR STR
08---------------------------------------------
09（020）12345678 （GZ）12345678 （020）<456123>78
10001517729C28 001517729C28 <517001>729C28
综合应用的例子：
01colrow_lineformata30;
02withsudokuas（
03 select'020000080568179234090000010030040050040205090070080040050000060289634175010000020'asline
04 fromdual
05）,
06tmpas（
07 selectregexp_substr（line,'\d{9}',1,level）row_line,
08 levelcol
09 fromsudoku
10 connectbylevel<=9
11）
12selectregexp_replace（row_line,'（\d）（\d）（\d）（\d）（\d）（\d）（\d）（\d）（\d）','\1\2\3\4\5\6\7\8\9'）row_line
13fromtmp
14
15ROW_LINE
16------------------------------
17020000080
18568179234
19090000010
20030040050
21040205090
22070080040
23050000060
24289634175
25010000020
ORACLE中的支持正则表达式的函数主要有下面四个：
1，REGEXP_LIKE：
与LIKE的功能相似
2，REGEXP_INSTR：
与INSTR的功能相似
3，REGEXP_SUBSTR：
与SUBSTR的功能相似
4，REGEXP_REPLACE：
与REPLACE的功能相似
它们在用法上与OracleSQL函数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位置和顺序
*/
--创建表
createtablefzq
（
idvarchar（4）,
valuevarchar（10）
）;
--数据插入
insertintofzqvalues
（'1','1234560'）;
insertintofzqvalues
（'2','1234560'）;
insertintofzqvalues
（'3','1b3b560'）;
insertintofzqvalues
（'4','abc'）;
insertintofzqvalues
（'5','abcde'）;
insertintofzqvalues
（'6','ADREasx'）;
insertintofzqvalues
（'7','123 45'）;
insertintofzqvalues
（'8','adc de'）;
insertintofzqvalues
（'9','adc,.de'）;
insertintofzqvalues
（'10','1B'）;
insertintofzqvalues
（'10','abcbvbnb'）;
insertintofzqvalues
（'11','11114560'）;
insertintofzqvalues
（'11','11124560'）;
--regexp_like
--查询value中以1开头60结束的记录并且长度是7位
select*fromfzqwherevaluelike'1____60';
select*fromfzqwhereregexp_like（value,'1....60'）;
--查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。
--使用like就不是很好实现了。
select*fromfzqwhereregexp_like（value,'1[0-9]{4}60'）;
--也可以这样实现，使用字符集。
select*fromfzqwhereregexp_like（value,'1[[:
digit:
]]{4}60'）;
--查询value中不是纯数字的记录
select*fromfzqwherenotregexp_like（value,'^[[:
digit:
]]+$'）;
--查询value中不包含任何数字的记录。
select*fromfzqwhereregexp_like（value,'^[^[:
digit:
]]+$'）;
--查询以12或者1b开头的记录.不区分大小写。
select*fromfzqwhereregexp_like（value,'^1[2b]','i'）;
--查询以12或者1b开头的记录.区分大小写。
select*fromfzqwhereregexp_like（value,'^1[2B]'）;
--查询数据中包含空白的记录。
select*fromfzqwhereregexp_like（value,'[[:
space:
]]'）;
--查询所有包含小写字母或者数字的记录。
select*fromfzqwhereregexp_like（value,'^（[a-z]+|[0-9]+）$'）;
--查询任何包含标点符号的记录。
select*fromfzqwhereregexp_like（value,'[[:
punct:
]]'）;
/*
理解它的语法就可以了。
其它的函数用法类似。
*/
升级会员 