oracle 正则表达式

什么是正则表达式？ 
  正则表达式由一个或多个字符和/或者字符元， 最简单的形式由字符组成， 像正则表达
式cat,被解释为字符c后面跟着 a和 t, 这样的模式匹配字符串有  cat, location 和 catalog. 
 字符元为Oracle 怎么处理正则表达式提供了运算规则。 如果, 你了解了各种字符元的意
思, 你就明白正则表达式在处理独立和代替文本字符方面是多么的强大. 
  数据有效性， 重复词的辨认, 无关的空白检测，或者分解多个正则组成的字符串， 可以
用来检查电话号码的有效性， 邮政编码，E-mail 地址， 社保号码，IP地址， 文件名和路
径， 等等。甚至可以用来模式定位,如HTML的标签，数字， 日期，或者任何模式匹配的
字符或者代替他们的模式 
在Oracle 10g 中使用正则表达式 
为了控制正则表达式可以使用 Oracle中新的REGEXP_LIKE, REGEXP_INSTR, 和
REG_EXP_REPLACE 函数， 你将会看到这些函数怎样增强了 LIKE, INSTR, SUBSTR 和
REPLACE 函数的功能. 实际上，和已经存在的函数呵操作相似 
/* 
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= '{m,}'表示至少出现 m 次。 
\num 匹配  num，其中  num 是一个正整数。对所获取的匹配的引用。 
 
字符簇：   
[[:alpha:]] 任何字母。 
[[:digit:]] 任何数字。 
[[:alnum:]] 任何字母和数字。 
[[:space:]] 任何白字符。 
[[:upper:]] 任何大写字母。 
[[:lower:]] 任何小写字母。 
[[:punct:]] 任何标点符号。 
[[:xdigit:]] 任何16 进制的数字，相当于[0-9a-fA-F]。 
各种操作符的运算优先级 
\转义符 
(),  (?:),  (?=),  [] 圆括号和方括号 
*,  +,  ?,  {n},  {n,},  {n,m} 限定符 
^,  $,  anymetacharacter 位置和顺序 
|   
*/ 
 
 
--创建表 
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'); 
--regexp_like --查询value 中以1 开头 60 结束的记录并且长度是 7 位 
select  *  from  fzq  where  value  like  '1____60'; 
select  *  from  fzq  where  regexp_like(value,'^1....60'); --注意1前面的的开始符号
--查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。 
--使用like 就不是很好实现了。 
select  *  from  fzq  where  regexp_like(value,'^1[0-9]{4}60'); --注意1前面的开始符号
-- 也可以这样实现，使用字符集。 
select  *  from  fzq  where  regexp_like(value,'1[[:digit:]]{4}60'); 
-- 查询value 中不是纯数字的记录 
select  *  from  fzq  where  not  regexp_like(value,'^[[:digit:]]+$'); 
-- 查询value 中不包含任何数字的记录。 
select  *  from  fzq  where  regexp_like(value,'^[^[:digit:]]+$'); 
--查询以12 或者1b开头的记录.不区分大小写。 
select  *  from  fzq  where  regexp_like(value,'^1[2b]','i'); 
--查询以12 或者1b开头的记录.区分大小写。 
select  *  from  fzq  where  regexp_like(value,'^1[2B]'); 
-- 查询数据中包含空白的记录。 
select  *  from  fzq  where  regexp_like(value,'[[:space:]]'); 
--查询所有包含小写字母或者数字的记录。 
select  *  from  fzq  where  regexp_like(value,'^([a-z]+|[0-9]+)$'); 
--查询任何包含标点符号的记录。 
select  *  from  fzq  where  regexp_like(value,'[[:punct:]]'); 
/* 
理解它的语法就可以了。其它的函数用法类似。 
*/   
 
---------------------------------------实际应用------------ 
   查询 包含  A,E,F 的 字段 
SQL>  Select 性质  From 部门 Where  Regexp_Like(性质,  '.*[A].*[E].*[F].*'); 
  
性质 
------------------------------------------------------------ 
A,B,C,D,E,F 
A,E,F,G 
A,B,C,D,E,F,G,H,I,J,L,M,N 
A,B,C,D,E,F,G,H,I,J,L,M,N 
A,E,F 
A,B,C,D,E,F,G 
A,E,F 
A,B,C,D,E,F,G,H,I,J,L,M,N 
A,E,F,G 
A,E,F 
A,E,F 
A,D,E,F 
  
12  rows  selected 
   
 
正则表达式的一个有用的特性是能够存储子表达式供以后重用；这也被称为后向
引用  。它允许复杂的替换功能，如在新的位置上交换模式或显示重复出现的单
词或字母。子表达式的匹配部分保存在临时缓冲区中。缓冲区从左至右进行编号，
并利用  /digit 符号进行访问，其中  digit 是  1 到  9 之间的一个数字，它匹配 
第  digit 个子表达式，子表达式用一组圆括号来显示。   
接下来的例子显示了通过按编号引用各个子表达式 
将姓名  Ellen Hildi Smith 转变为  Smith, Ellen Hildi。   SELECT REGEXP_REPLACE( 
'Ellen Hildi Smith', 
'(.*) (.*) (.*)', '/3, /1 /2') 
FROM dual 
REGEXP_REPLACE('EL 
------------------ 
Smith, Ellen Hildi   
该  SQL 语句显示了用圆括号括住的三个单独的子表达式。每一个单独的子表达
式包含一个匹配元字符  (.)，并紧跟着  * 元字符，表示任何字符（除换行符之
外）都必须匹配零次或更多次。空格将各个子表达式分开，空格也必须匹配。圆
括号创建获取值的子表达式，并且可以用  /digit 来引用。第一个子表达式被赋
值为  /1 ，第二个  /2，以此类推。这些后向引用被用在这个函数的最后一个参
数  (/3, /1 /2) 中，这个函数有效地返回了替换子字符串，并按期望的格式来
排列它们（包括逗号和空格）。 详细说明了该正则表达式的各个组成部分。 
后向引用对替换、格式化和代替值非常有用，并且您可以用它们来查找相邻出现
的值。接下来的例子显示了使用  REGEP_SUBSTR 函数来查找任意被空格隔
开的重复出现的字母数字值。显示的结果给出了识别重复出现的单词  is 的子字
符串。  
SELECT REGEXP_SUBSTR( 
'The final test is is the implementation', 
'([[:alnum:]]+)([[:space:]]+)/1') AS substr 
FROM dual 
SUBSTR 
------ 
is is  
正则表达式的实际应用   
您不仅可以在队列中使用正则表达式，还可以在使用  SQL  操作符或函数的任何
地方（比如说在  PL/SQL  语言中）使用正则表达式。您可以编写利用正则表达式
功能的触发器，以验证、生成或提取值。   
接下来的例子演示了您如何能够在一次列检查约束条件中应用  REGEXP_LIKE 
操作符来进行数据验证。它在插入或更新时检验正确的社会保险号码格式。如 
123-45-6789  和  123456789  之类格式的社会保险号码对于这种列约束条件是可
接受的值。有效的数据必须以三个数字开始，紧跟着一个连字符，再加两个数字
和一个连字符，最后又是四个数字。另一种表达式只允许  9  个连续的数字。竖
线符号  (|)  将各个选项分开。   
  ALTER TABLE students ADD CONSTRAINT stud_ssn_ck CHECK (REGEXP_LIKE(ssn, 
'^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$')) 
由  ^  和  $  指示的开头或结尾的字符都是不可接受的。确保您的正则表达式没有
分成多行或包含任何不必要的空格，除非您希望格式如此并相应地进行匹配。 
 
因为正则表达式有助于解决复杂的问题，所以它们是非常强大的。正则表达式的
一些功能难于用传统的  SQL  函数来仿效。 
当您了解了这种稍显神秘的语言的基础构建程序块时，正则表达式将成为您的工
具包的不可缺少的一部分（不仅在  SQL  环境下也在其它的编程语言环境下）。为
了使您的各个模式正确，虽然尝试和错误有时是必须的，但正则表达式的简洁和
强大是不容置疑的。   
表  1：定位元字符   
元字符  说明 
^  使表达式定位至一行的开头 
$  使表达式定位至一行的末尾   
表  2：量词或重复操作符   
量词  说明 
*  匹配  0 次或更多次 
?  匹配  0 次或  1 次 
+  匹配  1 次或更多次 
{m}  正好匹配  m 次 
{m,}  至少匹配  m 次 
{m, n}  至少匹配  m 次但不超过  n 次 
表  3：预定义的  POSIX 字符类   
字符类  说明 
[:alpha:]  字母字符 
[:lower:]  小写字母字符 
[:upper:]  大写字母字符 
[:digit:]  数字 [:alnum:]  字母数字字符 
[:space:]  空白字符（禁止打印），如回车符、换行符、竖直制表符和换页符 
[:punct:]  标点字符 
[:cntrl:]  控制字符（禁止打印） 
[:print:]  可打印字符 
表  4：表达式的替换匹配和分组   
元字符  说明 
|  替换  分隔替换选项，通常与分组操作符  () 一起使用 
( )  分组  将子表达式分组为一个替换单元、量词单元或后向引用单元（参见“后向引用”部分）  
[char] 
字符列
表 
表示一个字符列表；一个字符列表中的大多数元字符（除字符类、^ 和  - 元字符
之外）被理解为文字 
表  5：REGEXP_LIKE 操作符   
语法  说明 
REGEXP_LIKE(source_string, 
pattern 
[, match_parameter]) 
source_string 支持字符数据类型（CHAR、
VARCHAR2、 CLOB、 NCHAR、 NVARCHAR2 和  NCLOB，
但不包括  LONG）。 pattern 参数是正则表达式的另一个
名称。match_parameter 允许可选的参数（如处理换
行符、保留多行格式化以及提供对区分大小写的控制）。 
表  6：REGEXP_INSTR 函数   
语法  说明 
REGEXP_INSTR(source_string, 
pattern 
[, start_position 
[, occurrence 
[, return_option 
[, match_parameter]]]]) 
该函数查找  pattern ，并返回该模式的第一个位置。
您可以随意指定您想要开始搜索的 
start_position。  occurrence 参数默认为  1，
除非您指定您要查找接下来出现的一个模式。
return_option 的默认值为  0，它返回该模式的起始
位置；值为  1 则返回符合匹配条件的下一个字符的起始位
置。 
表  7：  5 位数字加  4 位邮政编码表达式的说明   语法  说明 
   必须匹配的空白 
[:digit:]  POSIX 数字类   
]  字符列表的结尾   
{5}  字符列表正好重复出现  5 次 
(  子表达式的开头   
-  一个文字连字符，因为它不是一个字符列表内的范围元字符 
[  字符列表的开头   
[:digit:]  POSIX [:digit:]类 
[  字符列表的开头   
]  字符列表的结尾 
{4}  字符列表正好重复出现  4 次   
)  结束圆括号，结束子表达式   
?  ? 量词匹配分组的子表达式  0 或  1 次，从而使得  4 位代码可选 
$  定位元字符，指示行尾 
表  8：REGEXP_SUBSTR 函数   
语法  说明 
REGEXP_SUBSTR(source_string, 
pattern 
[, position [, occurrence 
[, match_parameter]]]) 
REGEXP_SUBSTR 函数返回匹配模式的子字
符串。 
表  9：  REGEXP_REPLACE 函数   
语法  说明 
REGEXP_REPLACE(source_string, 
pattern 
[, replace_string [, position 
[,occurrence, 
[match_parameter]]]]) 
该函数用一个指定的  replace_string 来替换
匹配的模式，从而允许复杂的“搜索并替换”操作。 表  10：后向引用元字符   
元字符  说明 
/digit 
反斜
线 
紧跟着一个  1 到  9 之间的数字，反斜线匹配之前的用括号括起来的第  digit 个子
表达式。 
（注意：反斜线在正则表达式中有另一种意义，取决于上下文，它还可能表示 
Escape 字符。 
表  11：模式交换正则表达式的说明   
正则表达式项目  说明 
(  第一个子表达式的开头   
.  匹配除换行符之外的任意单字符 
*  重复操作符，匹配之前的  . 元字符  0 到  n 次 
) 
第一个子表达式的结尾；匹配结果在  /1 
中获取（在这个例子中，结果为  Ellen。） 
 
必须存在的空白   
(  第二个子表达式的开头   
.  匹配除换行符之外的任意单个字符   
*  重复操作符，匹配之前的  . 元字符  0 到  n 次 
) 
第二个子表达式的结尾；匹配结果在  /2 
中获取（在这个例子中，结果为  Hildi。） 
 
空白   
(  第三个子表达式的开头   
.  匹配除换行符之外的任意单字符   
*  重复操作符，匹配之前的  . 元字符  0 到  n 次 
) 
第三个子表达式的结尾；匹配结果在  /3 
中获取（在这个例子中，结果为  Smith。） 
表  12：社会保险号码正则表达式的说明   
正则表达式项目  说明 ^  行首字符（正则表达式在匹配之前不能有任何前导字符。） 
(  开始子表达式并列出用  | 元字符分开的可替换选项 
[  字符列表的开头 
[:digit:]  POSIX 数字类   
]  字符列表的结尾   
{3}  字符列表正好重复出现  3 次   
-  连字符   
[  字符列表的开头   
[:digit:]  POSIX 数字类 
]  字符列表的结尾   
{2}  字符列表正好重复出现  2 次   
-  另一个连字符   
[  字符列表的开头   
[:digit:]  POSIX 数字类   
]  字符列表的结尾   
{4}  字符列表正好重复出现  4 次   
|  替换元字符；结束第一个选项并开始下一个替换表达式 
[  字符列表的开头   
[:digit:]  POSIX 数字类 
]  字符列表的结尾 
{9}  字符列表正好重复出现  9 次   
)  结束圆括号，结束用于替换的子表达式组 
$  定位元字符，指示行尾；没有额外的字符能够符合模式 
     
 
     
 
 
 
函数举例 REGEXP_LIKE 与LIKE 操作符相似。如果第一个参数匹配正则表达式它就解析为TRUE。
例如Where REGEXP_LIKE(ENAME,'^J[AO]','i') 将在ENAME 以JA 或JO 开始的情况下
返回一行数据。'I' 参数指定正则表达式是大小写敏感的。另外还可以在 CHECK 约束和函
数索引中指定REGEXP_LIKE。例如： 
Alter TABLE EMP ADD CONSTRAINT REGEX01 
     CHECK (REGEXP_LIKE(ENAME,'^[[:alpha:]]+$')); 
这条语句使得ENAME 字段只能包含字母和数字字符（也就是说没有空格或者标点符号）。
试图插入或者更新这些数据将导致一个ORA-2290 异常，或者检查约束的有效性。 
REGEXP_INSTR 与INSTR 函数类似。它返回一个字符串中匹配一个正则表达式的第一个
子串的开始位置。例如： 
Select REGEXP_INSTR('The total is $400 for your purchase.','$[[:digit:]]+') 
FROM DUAL; 
这个查询返回14，即$400 在字符串的开始位置。另外还可以指定子串出现的次数；开始搜
索的位置；是返回匹配的位置还是返回匹配之后字符的位置。 
REGEXP_SUBSTR 返回匹配一个正则表达式的子串。虽然结合使用SUBSTR 和
REGEXP_INSTR 及LENGTH 也可以实现这一功能，但是使用这个函数却更为简单。 
Select REGEXP_INSTR('one,two,three','[^,]*') FROM DUAL; 
这个查询返回'one'，将第一个参数看成一个逗号分隔的列表并返回第一个逗号之前的所有字
符。 
REGEXP_REPLACE 返回初始参数被匹配子串替换之后的结果。例如： 
Select REGEXP_REPLACE('The temperature is 23°F', 
     '([[:digit:]])+°F', 
     ('/1'-32)*5/9||'°C') 
FROM DUAL; 
这个查询将查找一个华氏温度并将其转换为摄氏度。它将返回：'The temperature is -5°C'。 
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26509390/viewspace-1392636/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26509390/viewspace-1392636/