oracle中正则表达式的使用
使用正则表达式
目的
在本教程中，您将学习如何使用正则表达式支持。Oracle 数据库 10g 中引入了这个新特性。
大约 30 分钟
本教程包括下列主题：
 |
概述 |
|
前提条件 |
|
在 Oracle 数据库 10g 中使用正则表达式 |
|
使用基本搜索 |
|
使用多语言功能 |
|
正则表达式和校验约束 |
|
总结 |
注意：此外，您还可以在下列步骤中将鼠标放在每个单独的图标上，从而仅加载和查看与该步骤相关的屏幕截图。可以通过单击各个屏幕截图来将其隐藏。
在 Oracle 数据库 10g 中，可以使用 SQL 和 PL/SQL 实现正则表达式支持。正则表达式是一种描述简单和复杂的搜索和处理模式的方法。字符串处理和搜索在基于 Web 的应用程序的逻辑中占有很大比重。其用途从简单的任务（例如，在指定文本中查找单词 San Francisco）到复杂的任务（例如，提取文本中的所有 URL），再到更复杂的任务（例如，查找第二个字符是元音的所有单词）。
Oracle 数据库 10g 引入了对正则表达式的支持。该实现符合用于 UNIX 的可移植操作系统 (POSIX) 标准（由电气和电子工程师协会 (IEEE) 颁布）的 ASCII 数据匹配语意和语法。Oracle 的多语言功能使运算符的匹配功能超过了 POSIX 标准。
在与 SQL 一起使用时，正则表达式可以对 Oracle 数据库中存储的任何数据执行强大的搜索和处理。可以使用此特性轻松解决在其他情况下很难通过编程解决的问题。
前提条件
开始本教程之前，您应该：
| 1. | ||
| 2. |
下载 regexp.zip 文件并将其解压缩到您的工作目录 (c:\wkdir) 中。 |
|
匹配机制
如果有一个字符串 aabcd，并指定了一个 a(b|c)d 搜索，则将搜索后跟 b 或 c，接着是 d 的 a。
Regular Expression: 'a(b|c)d' |
| 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 | 在给定字符串中搜索某个正则表达式模式并返回匹配的子字符串 |
Oracle 数据库正则表达式中的 POSIX 元字符
元字符是具有特殊意义的字符，如通配符字符、重复字符、非匹配字符或一个字符范围。
可以在与函数匹配的模式中使用多个预定义的元字符符号。
| 符号 | 说明 |
*
|
匹配零个或多个匹配项 |
|
|
用于指定选择性匹配项的选择性运算符 |
^/$
|
匹配行的开头和结尾 |
[] |
用于匹配列表（匹配该列表中的任何表达式）的方括号表达式 |
[^exp] |
如果脱字符位于方括号内部，则对表达式取非。 |
{m}
|
精确匹配 m 次 |
{m,n}
|
至少匹配 m 次，但不超过 n 次 |
[: :] |
指定一个字符类并匹配该类中的任何字符 |
\ |
可以有四种不同的含义：(1) 表示其自身；(2) 引用下一个字符；(3) 引入一个运算符；(4) 不执行任何操作 |
+ |
匹配一个或多个匹配项 |
|
|
匹配零个或一个匹配项 |
. |
匹配所支持字符集中的任何字符（NULL 除外） |
() |
对表达式进行分组（视作一个子表达式） |
\n |
向后引用表达式 |
[==]
|
指定等价类 |
[..]
|
指定一个对照元素（如多字符元素） |
Perl 正则表达式扩展
除了 POSIX 标准以外，Oracle 还支持受 Perl 影响的常见元字符。如果您是一位生命科学开发人员，并使用 Perl 对大型 DNA 和蛋白质数据库中存储的生物信息数据进行模式分析，则可以使用 SQL 正则表达式直接支持数据，而无需中间层。这可以提供更高效的解决方案。为 Perl 兼容性而添加的元字符包括：
| 运算符 | 说明 |
\d
|
匹配数字字符 |
\D
|
匹配非数字字符 |
\w |
匹配单词字符 |
\W |
匹配非单词字符 |
\s |
匹配空白字符 |
\S |
匹配非空白字符 |
\A |
仅匹配字符串的开头 |
\Z |
仅匹配字符串的结尾或者行结尾之前 |
\z |
仅匹配字符串的结尾 |
* |
匹配 0 次或更多次（非贪婪） |
+ |
匹配 1 次或更多次（非贪婪） |
? |
匹配 0 次或 1 次（非贪婪） |
{n}
|
精确匹配 n 次（非贪婪） |
{n,}
|
至少匹配 n 次（非贪婪） |
{n,m}
|
至少匹配 n 次，但不超过 m 次（贪婪） |
有关 Oracle 正则表达式中受 Perl 影响的扩展的更多信息，请参阅《Oracle 数据库应用程序开发人员指南 — 基础 10g 第 2 版(10.2)》第 4 章“使用 Oracle 数据库中的正则表达式”。
您可以使用正则表达式功能来执行基本搜索。
正则表达式函数支持多语言功能，并可以用于对区域设置敏感的应用程序。要将正则表达式与 Oracle 的 NLS 语言功能结合使用，执行以下步骤：
| 1. |
执行以下脚本查找葡萄牙语的产品描述： @multiport.sql SELECT regexp_substr(translated_name, '^[a-z]+')
FROM oe.product_descriptions
WHERE language_id = 'PT'
AND translated_name like 'G%' ;
请注意，未显示该数据。
^ 位于方括号外部，这意味着您将搜索以任何字符（从 a 到 z）开头的任何字符串或子字符串。
|
| 2. |
执行同一查询，但这次使用不区分大小写的“i”。执行以下脚本： @multiport2.sql
SELECT regexp_substr(translated_name, '^[a-z]+', 1, 1, 'i')
FROM oe.product_descriptions
WHERE language_id = 'PT'
AND translated_name like 'G%' ;
|
| 3. |
由于在遇到非英文字符时，返回的字符串被截断，因此结果仍不完整。这是因为范围 [a-z] 对NLS_LANGUAGE 比较敏感。范围表达式对 NLS_SORT 很敏感，在本例中，它派生自NLS_LANGUAGE。您需要相应地设置 NLS_LANGUAGE 参数，以返回完整结果。执行以下查询： @multiport3.sql ALTER SESSION SET NLS_LANGUAGE=PORTUGUESE;
SELECT regexp_substr(translated_name, '^[a-z]+', 1, 1, 'i')
FROM oe.product_descriptions
WHERE language_id = 'PT'
AND translated_name like 'G%' ;
|
| 4. |
最后一步是查看用英语和葡萄牙语表示的结果以确已经完成了翻译。执行以下脚本： @multiport4.sql SELECT REGEXP_SUBSTR(i.product_name, '^[a-z]+', 1, 1, 'i') || ' = '
|| regexp_substr(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 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) ;
由于未执行验证，因此接受了未包含 @ 符号的电子邮件地址。 运行脚本之后，在开始下一步之前，在 SQL> 提示符处运行 ROLLBACK 语句。 ROLLBACK;
|
| 2. | 通过执行以下脚本实施约束：
@chkemail2.sql ALTER TABLE customers
ADD CONSTRAINT cust_email_addr
CHECK(REGEXP_LIKE(cust_email,'@'))NOVALIDATE ;
|
| 3. | 再次执行 chkemail.sql 测试该约束。
@chkemail.sql
由于电子邮件地址不含要求的符号，因此它违反了校验约束。NOVALIDATE 子句确保不检查现有数据。
|
| 4. |
通过执行以下脚本删除约束： @chkemail3.sql ALTER TABLE customers DROP CONSTRAINT cust_email_addr ;
|
