oracle10g数据库中如何使用正则表达式

匹配零个或多个匹配项

用于指定选择性匹配项的选择性运算符

匹配行的开头和结尾

用于匹配列表（匹配该列表中的任何表达式）的方括号表达式

如果脱字符位于方括号内部，则对表达式取非。

精确匹配 m 次

至少匹配 m 次，但不超过 n 次

指定一个字符类并匹配该类中的任何字符

可以有四种不同的含义：(1) 表示其自身；(2) 引用下一个字符；(3) 引入一个运算符；(4) 不执行任何操作

匹配一个或多个匹配项

匹配零个或一个匹配项

匹配所支持字符集中的任何字符（NULL 除外）

对表达式进行分组（视作一个子表达式）

向后引用表达式

指定等价类

指定一个对照元素（如多字符元素）

检查 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]');

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 ;

请注意， [^[::]] 表示一个字符类，并匹配该类中的任何字符； [:alpha:] 匹配任何字母字符。在本示例中，您将通过使用 ^ 对该表达式取非。

执行同一查询，但这次使用已区分大小写的"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%' ;

由于在遇到非英文字符时，返回的字符串被截断，因此结果仍不完整。这是因为范围 [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%' ;

最后一步是查看用英语和葡萄牙语表示的结果以确保已经完成了翻译。执行以下脚本：

@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;

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

@chkemail2.sql

chkemail2.sql 脚本包含以下 SQL：

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

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

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

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

@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 说明了该正则表达式示例的各个组成部分。

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

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

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

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

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

表 1：定位元字符

表 2：量词或重复操作符

表 3：预定义的 POSIX 字符类

表 4：表达式的替换匹配和分组

表 5：REGEXP_LIKE 操作符

表 6：REGEXP_INSTR 函数

表 7： 5 位数字加 4 位邮政编码表达式的说明

表 8：REGEXP_SUBSTR 函数

表 9： REGEXP_REPLACE 函数

表 10：后向引用元字符

表 11：模式交换正则表达式的说明

表 12：社会保险号码正则表达式的说明