OBE教程之oracle使用正则表达式

OBE 主页 >10gR2 单实例 > 应用程序开发

使用正则表达式

目的

在本教程中，您将学习如何使用正则表达式支持。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.

完成了教程在 Windows 上安装 Oracle 数据库 10g。

2.

下载 regexp.zip 文件并将其解压缩到您的工作目录 (c:\wkdir) 中。

返回主题列表

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

匹配机制

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

Regular Expression: 'a(b|c)d'String to Match:    'aabcd'

aabcd说明结果*    搜索 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 数据库中的正则表达式”。

返回主题列表

使用基本搜索

您可以使用正则表达式功能来执行基本搜索。

1.

从终端窗口中，切换到 wkdir 目录。

cd\wkdir

以如下方式设置 NLS_LANG 环境设置：

set NLS_LANG=.AL32UTF8

启动 SQL*Plus，通过用户 ID 和口令 oe/oe 连接到 Oracle。

sqlplus 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

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]]]])

在该函数中：

srcstr：是（被）搜索值
pattern：是正则表达式

position：是搜索的起始位置
occurrence：是要搜索的匹配项（即要查找的字符串）
return_option：指示匹配项的开头或结尾位置
match_option：提供了更改默认匹配的选项。它可以包含以下一个或多个值：

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

要搜索这些产品名称以找到第一个非字母字符（无论它是大写还是小写）的位置，执行以下脚本：

@reinstr.sql

COLUMN product_name FORMAT a45COLUMN non_alpha FORMAT 9999999999SELECT product_name, REGEXP_INSTR(product_name, '[^[:alpha:]]') non_alphaFROM oe.product_information ;

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

 

4.

REGEXP_SUBSTR 函数根据匹配项的模式返回给定字符串。检查语法：

REGEXP_SUBSTR(srcstr, pattern [, position
[, occurrence [, match_option]]])

在该函数中：

srcstr：是（被）搜索值
pattern：是正则表达式

position：是搜索的起始位置
occurrence：是要搜索的匹配项（即要查找的字符串）
match_option：提供了更改默认匹配的选项。它可以包含以下一个或多个值：

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

您希望从 CUSTOMERS 表中提取电子邮件名，但是只提取位于瑞士的客户的电子邮件名。为此，您需要返回CUST_EMAIL 列（该列位于NLS_TERRITORY 为 Switzerland 的客户的 @ 符号前面）中的内容。执行以下脚本：

@resubstr.sql

SELECT REGEXP_SUBSTR(cust_email, '[^@]+')FROM oe.customersWHERE nls_territory = 'SWITZERLAND' ; 

请注意，在本示例中，结果返回第一个没有 @ 符号的子字符串。

 

5.

REGEXP_REPLACE 函数使用替换字符串所替换的模式的每一个匹配项来返回给定字符串。检查语法：

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

在该函数中：

srcstr：是（被）搜索值
pattern：是正则表达式

position：是搜索的起始位置
occurrence：是要搜索的匹配项（即要查找的字符串）
replacestr：是替换模式的字符串
match_option：提供了更改默认匹配的选项。它可以包含以下一个或多个值：

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

要返回 PRODUCT_INFORMATION 表的CATALOG_URL 列中的信息，可以对该列执行完全扫描。但是，这将导致返回上百个行，这是因为它列出了多个目录域中的特定 HTML 页面位置。

在该示例中，您只想要查找单个域名本身，而不是它们所包含的低级页面。要查找不包含所有不必要信息的域名，使用 REGEXP_REPLACE 函数。执行以下脚本：

@rereplace.sql

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

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

http://该表达式首先查找该字符串文字；此处没有特殊的元字符。([^/]+)

然后，该表达式搜索一系列字符（只要它们不是斜线 (/)）。

.*

该表达式在用该部分遍历该字符串的剩部分时结束。

\1

匹配表达式替换为后向引用 1，它是在第一组括号中匹配的任何内容。

 

返回主题列表

使用多语言功能

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

1.

执行以下脚本查找葡萄牙语的产品描述：

@multiport.sql

SELECT regexp_substr(translated_name, '^[a-z]+')FROM   oe.product_descriptionsWHERE  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_descriptionsWHERE  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_descriptionsWHERE  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 iWHERE 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 ('[phone]'),'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 ; 

返回主题列表

总结

在本教程中，您学习如何：

使用正则表达式执行各种搜索以访问数据使用正则表达式访问用其他语言表示的数据

返回主题列表

将鼠标置于该图标上可以隐藏所有的屏幕截图。