正则表达式具有强大、便捷、高效的文本处理功能。能够添加、删除、分析、叠加、插入和修整各种类型的文本和数据。Oracle从10g开始支持正则表达式
</pre><pre name="code" class="html"> 下面通过一些例子来说明使用正则表达式来处理一些工作中常见的问题。
1. REGEXP_SUBSTR
正则表达式具有强大、便捷、高效的文本处理功能。能够添加、删除、分析、叠加、插入和修整各种类型的文本和数据。Oracle从10g开始支持正则表达式</pre><pre name="code" class="html"> 下面通过一些例子来说明使用正则表达式来处理一些工作中常见的问题。 1. REGEXP_SUBSTR
<span style="font-weight: normal;">REGEXP_SUBSTR 函数使用正则表达式来指定返回串的起点和终点，返回与source_string 字符集中的VARCHAR2 或CLOB 数据相同的字符串。 </span>
<span style="font-weight: normal;">语法：</span>
<span style="font-weight: normal;">语法：</span>
<span style="font-weight: normal;"> --1.REGEXP_SUBSTR与SUBSTR函数相同，返回截取的子字符串 </span><span style="font-weight: normal;"></span><pre name="code" class="sql">REGEXP_SUBSTR(srcstr, pattern [, position [, occurrence [, match_option]]])<span style="font-weight: normal;"> 注： srcstr 源字符串 pattern 正则表达式样式 match_option 匹配选项（区分大小写） </span>
<span style="font-weight: normal;">1.1从字符串中截取子字符串 </span>
<span style="font-weight: normal;">SELECT regexp_substr('1PSN/231_3253/ABc', '[[:alnum:]]+') FROM dual; </span>
<span style="font-weight: normal;"> Output: 1PSN </span>
[[:alnum:]]+ 表示匹配1个或者多个字母或数字字符。
<span style="font-weight: normal;"> SELECT regexp_substr('1PSN/231_3253/ABc', '[[:alnum:]]+', 1, 2) FROM dual; </span><span style="font-weight: normal;"> Output: 231 </span>与上面一个例子相比，多了两个参数
1）表示从源字符串的第一个字符开始查找匹配
2） 表示第2次匹配到的字符串 （默认值是“1”，如上例）
<span style="font-weight: normal;"> select regexp_substr('@@/231_3253/ABc','@*[[:alnum:]]+') from dual;
Output: 231 </span>[[:alnum:]]+ 表示匹配1个或者多个字母或数字字符
注意：需要注意“+”和“*”的区别
<span style="font-weight: normal;"> select regexp_substr('1@/231_3253/ABc','@+[[:alnum:]]*') from dual;
Output: @</span>[[:alnum:]]* 表示匹配0个或者多个字母或数字字符
<span style="font-weight: normal;"><pre name="code" class="html"> select regexp_substr('1@/231_3253/ABc','@+[[:alnum:]]+') from dual;
Output: Null </span>[[:alnum:]]+ 表示匹配1个或者多个字母或数字字符
select regexp_substr('@1PSN/231_3253/ABc125','[[:digit:]]+$') from dual;
<span style="font-weight: normal;"><pre name="code" class="sql"> Output: 125 </span>[[:digit:]]+$ 表示匹配1个或者多个数字结尾的字符
<span style="font-weight: normal;"></pre><pre name="code" class="sql"> select regexp_substr('@1PSN/231_3253/ABc','[^[:digit:]]+$') from dual;
Output: /ABc </span><span style="font-weight: normal;"> select regexp_substr('Tom_Kyte@oracle.com','[^@]+') from dual;
Output: Tom_Kyte </span>[^@]+ 表示匹配1个或者多个不是“@”的字符
<span style="font-weight: normal;"> select regexp_substr('1PSN/231_3253/ABc','[[:alnum:]]*',1,2) </span><span style="font-weight: normal;"> from dual;
Output: Null </span>[[:alnum:]]* 表示匹配0个或者多个字母或者数字字符
注：因为是匹配0个或者多个，所以这里第2次匹配的是“/”（匹配了0次），而不是“231”，所以结果是“Null”
1.2匹配重复出现 查找连续2个小写字母<span style="font-weight: normal;"> SELECT regexp_substr('Republicc Of Africaa', '([a-z])\1', 1, 1, 'i')
FROM dual;
Output: cc </span>
1.3其他一些匹配样式
查找网页地址信息
<span style="font-weight: normal;"> SELECT regexp_substr('Go to http://www.oracle.com/products and click on database', 'http://([[:alnum:]]+\.?){3,4}/?') RESULT
FROM dual
Output: http://www.oracle.com </span>提取csv字符串中的第三个值
<span style="font-weight: normal;"></pre><pre name="code" class="sql"> SELECT regexp_substr('1101,Yokohama,Japan,1.5.105', '[^,]+', 1, 3) AS output
FROM dual;
Output: Japan </span>注：这个通常用来实现字符串的列传行
字符串的列传行
<span style="font-weight: normal;"> SELECT regexp_substr('1101,Yokohama,Japan,1.5.105', '[^,]+', 1, LEVEL) AS output
FROM dual
CONNECT BY LEVEL <= length('1101,Yokohama,Japan,1.5.105') -
length(REPLACE('1101,Yokohama,Japan,1.5.105', ',')) + 1;</span><span style="font-weight: normal;"></pre><pre name="code" class="sql"> Output: 1101
Yokohama
Japan </span><span style="font-weight: normal;"> 1.5.105 </span>下面这个例子，查找源字符串中是否包含 kid 、kids或者kidding 这三个字符串
<span style="font-weight: normal;"> SELECT CASE
WHEN regexp_like('Why does a kid enjoy kidding with kids only?',
'kid(s|ding)*', 'i') THEN 'Match Found'
ELSE 'No Match Found'
END AS output
FROM dual; </span><span style="font-weight: normal;"> Output: Match Found </span>2. REGEXP_INSTR
语法：
--2.REGEXP_INSTR与INSTR函数相同，返回字符串位置
<span style="font-weight: normal;"> REGEXP_INSTR(srcstr, pattern [, position [, occurrence [, return_option [,match_option]]]]) </span>与REGEXP_SUBSTR一样，它也有变量pattern、position(开始位置)、occurrence 和match_parameter；这里主要介绍一下新参数return_option 的作用，它允许用户告诉Oracle，模式出现的时候，要返回什么内容。
--如果return_option 为0 则，Oracle 返回第一个字符出现的位置。这是默认值，与INSTR的作用相同
<span style="font-weight: normal;"><span style="font-size:12px;"> SELECT regexp_instr('abc1def',
'[[:digit:]]') output
FROM dual;
</span></span><span style="font-weight: normal;"><span style="font-size:12px;"> Output: 4 </span></span>--如果return_option 为1，则Oracle 返回跟在所搜索字符出现以后下一个字符的位置。
--例如，下面的查询返回了在串中发现的第一个数字后的位置：
<span style="font-weight: normal;"> SELECT regexp_instr('abc1def',
'[[:digit:]]',1,1,1) output
FROM dual;
Output: 5</span>Oracle正则表达式(regularexpression)
| 元字符 | 字符含义 | 举例说明 |
| ^ | 匹配字符串的开始位置(在[]中使用，此时它表示不接受该字符集合。 | ^a：匹配以a开始的字符串 [^a]：匹配不含a的字符串 |
| - | 当使用在a-m表示范围； 当使用在第一个字符时表示 连字符串，如[-abc] | |
| $ | 匹配字符结束位置 | ‘a$'：匹配以a结束的字符串 |
| . | 匹配除换行符 n之外的任何单字符。 | |
| ? | 匹配前面的子表达式零次或一次 | tr(y(ing)?)：可以表示try或者trying |
| * | 匹配前面的子表达式零次或多次 | |
| + | 匹配前面的子表达式一次或多次 | |
| () | 标记一个子表达式的开始和结束位置 | a(b)*可以匹配 ab,abb,abbb,youabb (c|d)匹配c或d |
| [] | 标记一个中括号表达式 | [cd]匹配选择c或d等同于 (c|d)。匹配的是单字符，[^cd]是匹配c,d之外的单字符。 [a-z]表示所有小写字母 |
| {m,n} | m= <出现次数 <=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。 | |
| | | 表示或者的关系。指明两项之间的一个选择 | |
| 字符簇 | 字符含义 | |
| [[:alpha:]] | 任何字母。 | |
| [[:digit:]] | [[:digit:]] 任何数字。 | |
| [[:alnum:]] | 任何字母和数 | |
| [[:space:]] | 任何白字符。 | |
| [[:upper:]] | 任何大写字母。 | |
| [[:lower:]] | 任何小写字母。 | |
| [[:punct:]] | 任何标点符号。 | |
| [[:xdigit:]] | 任何16进制的数字，相当于[0-9a-fA-F]。 |
Oracle支持正则表达式的内置函数
| 名称 | 语法 | 备注 |
| REGEXP_LIKE | REGEXP_LIKE (source_string, pattern [, match_parameter] ) | source_string： 源字符串 Pattern： 正则表达式 match_parameter： 匹配模式(i:不区分大小写；c:区分大小写；n:允许使用可以匹配任意字符串的操作符；m:将x作为一个包含多行的字符串。 |
| REGEXP_REPLACE | REGEXP_REPLACE (source_string, pattern [,replace_string] [,position] [,occurtence] [,match_parameter] ) | replace_string： 用于替换的字符串 Position： 开始搜索的起始位置 occurtence 指定替换第n次出现字符串 其他同上。 |
| REGEXP_SUBSTR | REGEXP_SUBSTR (source_string, pattern [,position [,occurrence [,match_parameter]]] ) | Position： 指定在字符串中准确位置，默认为1 Occurrence： 指定在源字符串匹配过程中相对其他字符串，哪个字符串应该匹配。例如 select regexp_substr('The zip code 80831 is for falcon, co', '[[:alpha:]]{3,}', 1, 3) from dual; 结果选择的是code而非The或zip。 |
| REGEXP_INSTR | REGEXP_INSTR (source_string, pattern [,start_position [,occurrence [,return_option [,match_parameter]]]] ) | start_position： 开始搜索位置 Occurrence： 第n次出现pattern，默认为1 return_option： 0：pattern的起始位置 1：pattern下一个字符起始位置 默认为0 |
| REGEXP_COUNT | REGEXP_COUNT (source_string, pattern [[,start_position] [,match_parameter]]) | 11g新增的函数，表示pattern在原字符串中出现的次数 start_position： 开始搜索的位置 |
Oracle支持正则表达式函数举例。
Regexp_substr：用来提取一个字符串的一部分。
Regexp_substr('first filed, second filed, thirdfiled', ', [^,]*,')Pattern为', [^,]*,'表示以，开始逗号，中间空一个格，连续0或多个非逗号字符，最后还是逗号，这样，返回则为', second filed,'。
regexp_instr('Jone Smith,10045 Berry Lane,SanJoseph,CA
91234-1234','[[:digit:]]{5}([-[[:digit:]]{4}])?$')
Regexp_instr:用来pattern的起始位置。
如有一组地址，返回邮编的位置
regexp_instr('Jone Smith,10045 Berry Lane,SanJoseph,CA
91234-1234','[[:digit:]]{5}([-[[:digit:]]{4}])?$')显然结果位置是91234-1234对应的起始位置。
-------------------------------------------------------------------------------
Regexp_replace：用来将pattern替换成需要替换的字符串，相比传统的replace函数，该函数相比较灵活。例如：
'Jone smith'中有三个空格，而'Jone smith'有2个空格，要将中间的空格改为一个空格，使用replace就要使用2次，而是用正则表达式，我们就就可以将pattern设计为
'( ){2,}'这样
regexp_replace(mc,'( ){2,}',' ')
-------------------------------------------------------------------------------
regexp_like是增强版的like，可以包含_和%通配符，使用在where条件中。
正则表达式特性---后向引用
通过将自表达式匹配的内容缓存到缓冲区中，从左相右进行编号，以/digit（digit为1-9）。而子表达式用圆括号表示。
1.在regexp_replace中应用：
将字符串'aa bb cc'反写成'cc bb aa'则可以设计如下：
regexp_replace('aa bbcc','(.*) (.*) (.*)','\3 \2 \1')
(.*)则表示任何一个字符串组合，三个这样的组合用空格隔开，与源字符串吻合，\1,\2,\3则分别存储了三个字表达式的值，这样我们就可以使用这个达到目的。
2.在regexp_substr中应用：
查找被空格隔开的重复字母数字值
regexp_substr('the finaltest is is the implention','([[:alnum:]]+)([[:space:]]+)\1')
Select REGEXP_REPLACE('aa bb cc','(.*) (.*) (.*)', '3, 2, 1') FROM dual；
REGEXP_REPLACE('ELLENHILDISMIT
cc, bb, aa create table test(mc varchar2(60));
insert into test values('112233445566778899');
insert into test values('22113344 5566778899');
insert into test values('33112244 5566778899');
insert into test values('44112233 5566 778899');
insert into test values('5511 2233 4466778899');
insert into test values('661122334455778899');
insert into test values('771122334455668899');
insert into test values('881122334455667799');
insert into test values('991122334455667788');
insert into test values('aabbccddee');
insert into test values('bbaaaccddee');
insert into test values('ccabbddee');
insert into test values('ddaabbccee');
insert into test values('eeaabbccdd');
insert into test values('ab123');
insert into test values('123xy');
insert into test values('007ab');
insert into test values('abcxy');
insert into test values('The final test is is is how to find duplicate words.');
commit;
select * from test where regexp_like(mc,'^a{1,3}');
select * from test where regexp_like(mc,'a{1,3}');
select * from test where regexp_like(mc,'^a.*e$');
select * from test where regexp_like(mc,'^[[:lower:]]|[[:digit:]]');
select * from test where regexp_like(mc,'^[[:lower:]]');
Select mc FROM test Where REGEXP_LIKE(mc,'[^[:digit:]]');
Select mc FROM test Where REGEXP_LIKE(mc,'^[^[:digit:]]');
Select REGEXP_INSTR(mc,'[[:digit:]]$') from test;
Select REGEXP_INSTR(mc,'[[:digit:]]+$') from test;
Select REGEXP_INSTR('The price is $400.','$[[:digit:]]+') FROM DUAL;
Select REGEXP_INSTR('onetwothree','[^[[:lower:]]]') FROM DUAL;
Select REGEXP_INSTR(',,,,,','[^,]*') FROM DUAL;
Select REGEXP_INSTR(',,,,,','[^,]') FROM DUAL; SELECT REGEXP_SUBSTR(mc,'[a-z]+') FROM test;
SELECT REGEXP_SUBSTR(mc,'[0-9]+') FROM test;
SELECT REGEXP_SUBSTR('aababcde','^a.*b') FROM DUAL;
Select REGEXP_REPLACE('Joe Smith','( ){2,}', ',') AS RX_REPLACE FROM dual;
Select REGEXP_REPLACE('aa bb cc','(.*) (.*) (.*)', '3, 2, 1') FROM dual；
SQL> select * from test;
扫一扫
879
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
