在 Oracle 数据库 10g 中使用正则表达式

从OTN弄来的,非常有用,前面我的一个 神奇,吉祥号码查找   用的就是正则表达式过滤的

----------------------------------------------------------------------------------------------------------------------------

在 Oracle 数据库 10g   中使用正则表达式

匹配机制

如果有一个字符串  aabcd ，并指定了一个  a(b|c)d   搜索，则将搜索后跟  b   或  c ，接着是  d   的  a 。

正则表达式： 'a(b|c)d'
匹配的字符串： 'aabcd'

a	a	b	c	d	说明	结果
*					搜索  a   并成功	匹配
	*				搜索  b   但失败	不匹配
	*				搜索  c   但失败，重置并继续搜索	不匹配
	*				搜索  a   并成功	匹配
		*			搜索  b   并成功；将  c   记忆为一个选择项	匹配
			*		搜索  d   但失败	不匹配
		*			搜索作为上次记忆的选择项的  c   但失败，重置并继续搜索	不匹配
		*			搜索  a   但失败，重置并继续搜索	不匹配
			*		搜索  a   但失败，重置并继续搜索	不匹配
				*	搜索  a   但失败，重置并继续搜索	不匹配

a(b|c)d   不匹配给定的字符串  aabcd 。

要在 SQL 或 PL/SQL 中实现正则表达式支持，需要使用一组新函数。这些函数是：

函数名	说明
REGEXP_LIKE	类似于 LIKE 运算符，但执行正则表达式匹配而不是简单的模式匹配
REGEXP_INSTR	在给定字符串中搜索某个正则表达式模式，并返回匹配项的位置。
REGEXP_REPLACE	搜索某个正则表达式模式并使用替换字符串替换它
REGEXP_SUBSTR	在给定字符串中搜索某个正则表达式模式并返回匹配的子字符串

元字符

元字符是具有特殊意义的字符，如通配符字符、重复字符、非匹配字符或一个字符范围。

可以在与函数匹配的模式中使用多个预定义的元字符符号。

符号	说明
*	匹配零个或多个匹配项
|	用于指定选择性匹配项的选择性运算符
^/$	匹配行的开头和结尾
[]	用于匹配列表（匹配该列表中的任何表达式）的方括号表达式
[^exp]	如果脱字符位于方括号内部，则对表达式取非。
{m}	精确匹配  m   次
{m,n}	至少匹配  m   次，但不超过  n   次
[: :]	指定一个字符类并匹配该类中的任何字符
\	可以有四种不同的含义：(1) 表示其自身；(2) 引用下一个字符；(3) 引入一个运算符；(4) 不执行任何操作
+	匹配一个或多个匹配项
?	匹配零个或一个匹配项
.	匹配所支持字符集中的任何字符（NULL 除外）
()	对表达式进行分组（视作一个子表达式）
\n	向后引用表达式
[==]	指定等价类
[..]	指定一个对照元素（如多字符元素）

 

使用基本搜索

以下示例演示了正则表达式函数的用法。执行以下步骤：

1.	在终端窗口中，切换到  /home/oracle/wkdir   目录并启动 SQL*Plus。 使用用户 ID  oe/oe   和口令  oe/oe   连接到 Oracle。 cd /home/oracle/wkdirsqlplus oe/oe
2.	检查 REGEXP_LIKE 函数的语法：   REGEXP_LIKE(srcstr, pattern [,match_option]) 在此函数中： srcstr：搜索值 pattern：正则表达式 match_option：用于更改默认匹配的选项。可以包含以下一个或多个值： “c”：使用区分大小写的匹配（默认值） “i”：使用区分大小写的匹配 “n”：允许匹配任何字符的运算符 “m”：将源字符串作为多行处理 要找到 PRODUCT_INFORMATION 表的 PRODUCT_NAME 列中名称包含 SSP/S、SSP/V、SSS/V 或 SSS/S 的所有产品，请执行以下脚本： @relike.sql relike.sql   脚本包含以下 SQL： SELECT product_nameFROM oe.product_informationWHERE regexp_like (product_name, 'SS[PS]/[VS]');
3.	REGEXP_INSTR   函数返回字符串中给定模式的位置。检查语法：   REGEXP_INSTR(srcstr, pattern [, position [, occurrence[, return_option [, match_option]]]]) 在该函数中： position:搜索起始位置 occurrence：要搜索的匹配项 return_option：指示匹配项的开头或结尾位置 match_option：用于更改默认匹配的选项。可以包含以下一个或多个值： “c”：使用区分大小写的匹配（默认值） “i”：使用区分大小写的匹配 “n”：允许匹配任何字符的运算符 “m”：将源字符串作为多行处理 要搜索产品名称以确定第一个非字母字符（无论其是大写还是小写）的位置，请执行以下脚本： @reinstr.sql reinstr.sql   脚本包含以下 SQL： COLUMN non_alpha FORMAT 9999999999 SELECT product_name, REGEXP_INSTR(product_name, '[^[:alpha:]]')non_alpha FROM   oe.product_information ; 请注意， [^[:<class>:]] 表示一个字符类，并匹配该类中的任何字符； [:alpha:] 匹配任何字母字符。在本示例中，您将通过使用 ^ 对该表达式取非。

4.

REGEXP_SUBSTR 函数根据匹配项的模式返回给定字符串。检查语法：   REGEXP_SUBSTR(srcstr, pattern [, position[, occurrence [, match_option]]]) 在该函数中： position:搜索起始位置 occurrence：要搜索的匹配项 match_option：用于更改默认匹配的选项。可以包含以下一个或多个值： “c”：使用区分大小写的匹配（默认值） “i”：使用区分大小写的匹配 “n”：允许匹配任何字符的运算符 “m”：将源字符串作为多行处理 要从 CUSTOMERS 表中提取电子邮件名，只提取位于瑞士的客户的电子邮件名。为此，返回 CUST_EMAIL 列（该列在客户的 @ 符号前的 NLS_TERRITORY 等于 Switzerland）中的内容。执行以下脚本： @resubstr.sql resubstr.sql   脚本包含以下 SQL： SELECT REGEXP_SUBSTR(cust_email, '[^@]+') FROM   oe.customers WHERE  nls_territory = 'SWITZERLAND' ; 请注意，在本示例中，结果返回第一个没有 @ 符号的子字符串。

 5.

 EGEXP_REPLACE 函数返回给定字符串中的“已替换的”子字符串。检查语法：

  REGEXP_REPLACE(srcstr, pattern [,replacestr [, position[, occurrence [, match_option]]]])

在该函数中：

position:搜索起始位置
occurrence：要搜索的匹配项
replacestr：替换模式的字符串
match_option：用于更改默认匹配的选项。可以包含以下一个或多个值：

“c”：使用区分大小写的匹配（默认值）
“i”：使用区分大小写的匹配
“n”：允许匹配任何字符的运算符
“m”：将源字符串作为多行处理

要返回 PRODUCT_INFORMATION 表的 CATALOG_URL 列中的信息，可以对该列执行完全扫描。但这将导致返回上百个行，这是因为它列出了多个目录域中的特定 HTML 页面位置。但在该示例中，您只想要查找单个域名本身，而不是它们所包含的低级页面。要查找不包含所有不必要信息的域名，请使用 REGEXP_REPLACE 函数。执行以下脚本：

@rereplace.sql

rereplace.sql   脚本包含以下 SQL：

SELECT UNIQUE REGEXP_REPLACE (catalog_url, 'http://([^/]+).*', '\1')
FROM oe.product_information ;

以下是有关如何处理字符串的介绍：

http://	该表达式首先查找该字符串文字；此处没有特殊的元字符。
([^/]+)	然后，该表达式搜索一系列字符（只要它们不是斜线 (/)）。
.*	该表达式在用该部分遍历该字符串的剩余部分时结束。
\1	匹配表达式替换为后向引用 1，它是在第一组括号中匹配的任何内容。

 

 

使用多语言功能

正则表达式函数支持多语言功能，并可以用于对区域设置敏感的应用程序。要将正则表达式与 Oracle 的 NLS 语言特性组合使用，请执行以下步骤：

1.	执行以下脚本以查找用葡萄牙语表示的产品描述： @multiport.sql multiport.sql   脚本包含以下 SQL： SELECT regexp_substr(to_char(translated_name), '^[a-z]+')FROM   oe.product_descriptionsWHERE  language_id = 'PT'AND    translated_name like 'G%' ; 请注意，未显示该数据。 ^ 位于方括号外部，这意味着您将搜索以任何字符（从 a 到 z）开头的任何字符串或子字符串。
2.	执行同一查询，但这次使用已区分大小写的“i”。执行以下脚本： @multiport2.sql multiport2.sql   脚本包含以下 SQL： SELECT regexp_substr(to_char(translated_name), '^[a-z]+', 1, 1, 'i')FROM   oe.product_descriptionsWHERE  language_id = 'PT'AND    translated_name like 'G%' ;
3.	由于在遇到非英文字符时，返回的字符串被截断，因此结果仍不完整。这是因为范围 [a-z] 对 NLS_LANGUAGE 比较敏感。因此，需要相应地设置 NLS_LANGUAGE 参数，以返回完整结果。执行以下查询： @multiport3.sql multiport3.sql   脚本包含以下 SQL： ALTER SESSION SET NLS_LANGUAGE=PORTUGUESE;SELECT regexp_substr(to_char(translated_name), '^[a-z]+', 1, 1, 'i')FROM   oe.product_descriptionsWHERE  language_id = 'PT'AND    translated_name like 'G%' ;
4.	最后一步是查看用英语和葡萄牙语表示的结果以确保已经完成了翻译。执行以下脚本： @multiport4.sql multiport4.sql   脚本包含以下 SQL： SELECT REGEXP_SUBSTR(i.product_name, '^[a-z]+', 1, 1, 'i') || ' = ' || regexp_substr(to_char(d.translated_name), '^[a-z]+', 1, 1, 'i') FROM   oe.product_descriptions d, oe.product_information i WHERE  d.language_id = 'PT' AND    d.translated_name like 'G%' AND    i.product_id = d.product_id ;ALTER SESSION SET NLS_LANGUAGE=AMERICAN;

返回主题列表

正则表达式和校验约束

要在校验约束中使用正则表达式，请执行以下步骤：

1.

在 CUSTOMERS 表的 CUST_EMAIL 列上添加一个校验约束可以确保只接受包含  @   符号的字符串。执行以下脚本：

@chkemail.sql

chkemail.sql   脚本包含以下 SQL：

INSERT INTO customers VALUES(9999,'Christian','Patel',            cust_address_typ ('1003 Canyon Road','87501','Santa Fe','NM','US'),            phone_list_typ ('[phone]'),'us','AMERICA','100',            'ChrisP+creme.com', 149, null, null, null, null, null) ;

由于未执行验证，因此接受了未包含 @ 符号的电子邮件地址。在开始进行下个步骤之前执行回滚。

ROLLBACK;

 

 2.

通过执行以下脚本实施约束：

@chkemail2.sql

chkemail2.sql   脚本包含以下 SQL：

ALTER TABLE customersADD CONSTRAINT cust_email_addrCHECK(REGEXP_LIKE(cust_email,'@'))NOVALIDATE ;

 

 3.

 再次执行  @chkemail.sql   测试该约束。

由于电子邮件地址不包含要求的符号，因此它违反了校验约束。NOVALIDATE 子句确保不检查现有数据。

 

 4.

 通过执行以下脚本删除约束：

@chkemail3.sql

chkemail3.sql   脚本包含以下 SQL：

ALTER TABLE customers DROP CONSTRAINT cust_email_addr ;

 

 后向引用

 正则表达式的一个有用的特性是能够存储子表达式供以后重用；这也被称为后向引用（在表 10   中对其进行了概述）。它允许复杂的替换功能，如在新的位置上交换模式或显示重复出现的单词或字母。子表达式的匹配部分保存在临时缓冲区中。缓冲区从左至右进行编号，并利用  \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 ) 中，这个函数有效地返回了替换子字符串，并按期望的格式来排列它们（包括逗号和空格）。表 11   详细说明了该正则表达式的各个组成部分。

 后向引用对替换、格式化和代替值非常有用，并且您可以用它们来查找相邻出现的值。接下来的例子显示了使用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})$'))

 由  ^   和  $   指示的开头或结尾的字符都是不可接受的。确保您的正则表达式没有分成多行或包含任何不必要的空格，除非您希望格式如此并相应地进行匹配。表 12   说明了该正则表达式示例的各个组成部分。

接下来的步骤

 访问 Oracle Database 10g 页面：
/global/cn/products/database/oracle10g/index.html

 将正则表达式与现有的功能进行比较

 正则表达式有几个优点优于常见的  LIKE   操作符和INSTR、SUBSTR   及  REPLACE   函数的。这些传统的 SQL 函数不便于进行模式匹配。只有  LIKE   操作符通过使用  %   和_   字符匹配，但  LIKE   不支持表达式的重复、复杂的更替、字符范围、字符列表和 POSIX 字符类等等。此外，新的正则表达式函数允许检测重复出现的单词和模式交换。这里的例子为您提供了正则表达式领域的一个概览，以及您如何能够在您的应用程序中使用它们。

 实实在在地丰富您的工具包

 因为正则表达式有助于解决复杂的问题，所以它们是非常强大的。正则表达式的一些功能难于用传统的 SQL 函数来仿效。当您了解了这种稍显神秘的语言的基础构建程序块时，正则表达式将成为您的工具包的不可缺少的一部分（不仅在 SQL 环境下也在其它的编程语言环境下）。为了使您的各个模式正确，虽然尝试和错误有时是必须的，但正则表达式的简洁和强大是不容置疑的。

 Alice Rischert   ([e-mail] ) 是哥伦比亚大学计算机技术与应用系的数据库应用程序开发和设计方向的主席。她编写了  Oracle SQL 交互手册 第 2 版    （Prentice Hall，2002）和即将推出的  Oracle SQL 示例   （Prentice Hall，2003）。Rischert 拥有超过 15 年的经验在财富 100 强公司内担任数据库设计师、DBA 和项目主管，并且她自从 Oracle version 5 起就一直使用 Oracle 产品。

 表 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 次
)	结束圆括号，结束用于替换的子表达式组
$	定位元字符，指示行尾；没有额外的字符能够符合模式