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

使用基本搜索

使用多语言功能

正则表达式和校验约束

完成了教程：使用真正应用集群 (RAC) 和自动存储管理 (ASM) 在 Windows 上安装 Oracle 数据库 10g。

或者

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

正则表达式：'a(b|c)d'
匹配的字符串：'aabcd'

*

匹配零个或多个匹配项

|

用于指定选择性匹配项的选择性运算符

^/$
     

匹配行的开头和结尾

[]

用于匹配列表（匹配该列表中的任何表达式）的方括号表达式

[^exp]

如果脱字符位于方括号内部，则对表达式取非。

{m}

精确匹配 m 次

{m,n}

至少匹配 m 次，但不超过 n 次

[: :]

指定一个字符类并匹配该类中的任何字符

\

可以有四种不同的含义：(1) 表示其自身；(2) 引用下一个字符；(3) 引入一个运算符；(4) 不执行任何操作

+

匹配一个或多个匹配项

?

匹配零个或一个匹配项

.

匹配所支持字符集中的任何字符（NULL 除外）

()

对表达式进行分组（视作一个子表达式）

\n

向后引用表达式

[==]
     

指定等价类

[..]
     

指定一个对照元素（如多字符元素）

在终端窗口中，切换到 /home/oracle/wkdir 目录并启动 SQL*Plus。

使用用户 ID oe/oe 和口令 oe/oe 连接到 Oracle。

cd /home/oracle/wkdir
sqlplus oe/oe

检查 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]');

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 ;

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

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' ; 

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

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 ;

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

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

@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）开头的任何字符串或子字符串。

执行同一查询，但这次使用已区分大小写的“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%' ;

由于在遇到非英文字符时，返回的字符串被截断，因此结果仍不完整。这是因为范围 [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%' ;

最后一步是查看用英语和葡萄牙语表示的结果以确保已经完成了翻译。执行以下脚本：

@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;

在 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;

通过执行以下脚本实施约束：

@chkemail2.sql

chkemail2.sql 脚本包含以下 SQL：

ALTER TABLE customers 
ADD CONSTRAINT cust_email_addr 
CHECK(REGEXP_LIKE(cust_email,'@'))NOVALIDATE ;

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

由于电子邮件地址不包含要求的符号，因此它违反了校验约束。NOVALIDATE 子句确保不检查现有数据。

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

@chkemail3.sql

chkemail3.sql 脚本包含以下 SQL：

ALTER TABLE customers DROP CONSTRAINT cust_email_addr ;