oracle中正则表达式的使用

使用正则表达式

在本教程中,您将学习如何使用正则表达式支持。Oracle 数据库 10g 中引入了这个新特性。

大约 30 分钟

主题

本教程包括下列主题:

注意:此外,您还可以在下列步骤中将鼠标放在每个单独的图标上,从而仅加载和查看与该步骤相关的屏幕截图。可以通过单击各个屏幕截图来将其隐藏。

概述

在 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‘
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 数据库应用程序开发人员指南 — 基础 10第 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 ; 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。