oracle里面的正则表达式,oracle 使用正则表达式

使用正则表达式

目的

在本教程中，您将学习如何使用正则表达式支持。Oracle 数据库 10g

中引入了这个新特性。

所需时间

大约 30 分钟

主题

本教程包括下列主题：

查看屏幕截图

将光标置于此图标上以加载和查看本教程的所有屏幕截图。(警告：此操作会同时加载所有屏幕截图，网速较慢时，响应时间可能会比较长。)

注意：您还可以在下面的步骤中将光标放在每个单独的图标上，仅加载和查看与该步骤相关的屏幕截图。

概述

在 Oracle 数据库 10g

中，可以使用 SQL 和 PL/SQL 实现正则表达式支持。正则表达式是一种描述搜索和处理简单模式和复杂的方法。在基于 Web

的应用程序的逻辑中字符串处理和搜索占有很大比重。其用途从简单的任务(例如，在指定文本中查找单词 San

Francisco)到复杂的任务(例如，提取文本中的所有

URL)，再到更复杂的任务(例如，查找第二个字符是元音的所有单词)。

Oracle 数据库 10g

引入了对正则表达式的支持。该实现符合用于 UNIX 的可移植操作系统 (POSIX) 标准(由电气和电子工程师协会 (IEEE)

颁布)的 ASCII 数据匹配语意和语法。Oracle 的多语言功能使运算符的匹配功能超过了 POSIX 标准。

在与 SQL 一起使用时，正则表达式可以对 Oracle

数据库中存储的任何数据执行强大的搜索和处理。可以使用此特性轻松解决在其他情况下很难通过编程解决的问题。

前提条件

开始学习本教程之前，您应已经：

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

匹配机制

如果有一个字符串 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/wkdir

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

relike.sql 脚本包含以下 SQL：

SELECT product_name

FROM oe.product_information

WHERE regexp_like (product_name, 'SS[PS]/[VS]');

3.

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:]匹配任何字母字符。在本示例中，您将通过使用

^ 对该表达式取非。

4.

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

REGEXP_SUBSTR(srcstr, pattern [, position

[, occurrence [, match_option]]])

在该函数中：

position:搜索起始位置

occurrence：要搜索的匹配项

match_option：用于更改默认匹配的选项。可以包含以下一个或多个值：

“c”：使用区分大小写的匹配(默认值)

“i”：使用区分大小写的匹配

“n”：允许匹配任何字符的运算符

“m”：将源字符串作为多行处理

要从 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:搜索起始位置

occurrence：要搜索的匹配项

replacestr：替换模式的字符串

match_option：用于更改默认匹配的选项。可以包含以下一个或多个值：

“c”：使用区分大小写的匹配(默认值)

“i”：使用区分大小写的匹配

“n”：允许匹配任何字符的运算符

“m”：将源字符串作为多行处理

要返回 PRODUCT_INFORMATION 表的 CATALOG_URL

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

页面位置。但在该示例中，您只想要查找单个域名本身，而不是它们所包含的低级页面。要查找不包含所有不必要信息的域名，请使用

REGEXP_REPLACE 函数。执行以下脚本：

@rereplace.sql

rereplace.sql 脚本包含以下 SQL：

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

FROM oe.product_information ;

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

http://

该表达式首先查找该字符串文字；此处没有特殊的元字符。

([^/]+)

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

.*

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

\1

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

使用多语言功能

正则表达式函数支持多语言功能，并可以用于对区域设置敏感的应用程序。要将正则表达式与

Oracle 的 NLS 语言特性组合使用，请执行以下步骤：

1.

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

@multiport.sql

multiport.sql 脚本包含以下 SQL：

SELECT regexp_substr(to_char(translated_name), '^[a-z]+')

FROM  oe.product_descriptions

WHERE 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_descriptions

WHERE 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_descriptions

WHERE 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 customers

ADD CONSTRAINT cust_email_addr

CHECK(REGEXP_LIKE(cust_email,'@'))NOVALIDATE ;

3.

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

由于电子邮件地址不包含要求的符号，因此它违反了校验约束。NOVALIDATE

子句确保不检查现有数据。

4.

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

@chkemail3.sql

chkemail3.sql 脚本包含以下 SQL：

ALTER TABLE customers DROP CONSTRAINT cust_email_addr ;