在 Oracle 数据库 10g 中使用正则表达式
http://rikugun.javaeye.com/blog/523636
匹配机制
如果有一个字符串 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:搜索值 “c”:使用区分大小写的匹配(默认值) 要找到 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:搜索起始位置 “c”:使用区分大小写的匹配(默认值) 要搜索产品名称以确定第一个非字母字符(无论其是大写还是小写)的位置,请执行以下脚本: @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:搜索起始位置 “c”:使用区分大小写的匹配(默认值) 要从 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:搜索起始位置 “c”:使用区分大小写的匹配(默认值) 要返回 PRODUCT_INFORMATION 表的 CATALOG_URL 列中的信息,可以对该列执行完全扫描。但这将导致返回上百个行,这是因为它列出了多个目录域中的特定 HTML 页面位置。但在该示例中,您只想要查找单个域名本身,而不是它们所包含的低级页面。要查找不包含所有不必要信息的域名,请使用 REGEXP_REPLACE 函数。执行以下脚本: @rereplace.sql rereplace.sql 脚本包含以下 SQL: SELECT UNIQUE REGEXP_REPLACE (catalog_url, 'http://([^/]+).*', '\1') FROM oe.product_information ; 以下是有关如何处理字符串的介绍:
|
使用多语言功能
正则表达式函数支持多语言功能,并可以用于对区域设置敏感的应用程序。要将正则表达式与 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 ('+1 505 243 4144'),'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( 该 SQL 语句显示了用圆括号括住的三个单独的子表达式。每一个单独的子表达式包含一个匹配元字符 (. ),并紧跟着* 元字符,表示任何字符(除换行符之外)都必须匹配零次或更多次。空格将各个子表达式分开,空格也必须匹配。圆括号创建获取值的子表达式,并且可以用 \digit 来引用。第一个子表达式被赋值为 \1 ,第二个 \2 ,以此类推。这些后向引用被用在这个函数的最后一个参数 (\3, \1 \2 ) 中,这个函数有效地返回了替换子字符串,并按期望的格式来排列它们(包括逗号和空格)。表 11 详细说明了该正则表达式的各个组成部分。 后向引用对替换、格式化和代替值非常有用,并且您可以用它们来查找相邻出现的值。接下来的例子显示了使用REGEP_SUBSTR 函数来查找任意被空格隔开的重复出现的字母数字值。显示的结果给出了识别重复出现的单词 is 的子字符串。 SELECT REGEXP_SUBSTR( 匹配参数选项 您可能已经注意到了正则表达式操作符和函数包含一个可选的匹配参数。这个参数控制是否区分大小写、换行符的匹配和保留多行输入。 正则表达式的实际应用 您不仅可以在队列中使用正则表达式,还可以在使用 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 产品。
|