ORACLE10正则表达式（二）

oracle10G增加的正则表达式功能！强!

Oracle数据库10g将正则表达式引入到SQL语句中
Oracle数据库10g将正则表达式引入到SQL语句中。

正则表达式是一种用于描述和操纵文本数据的功能强大的工具。事实上，正则表达式来源于早期的低级UNIX 工具（如qed和ed），现在它已经变得几乎无处不在，并且得到了各种广泛的编程和脚本语言、文本编辑器以及现在的Oracle Database 10g的支持。

什么是正则表达式？

正则表达式都是关于模式的。它使你能够使用文本数据来描述模式。一旦你能够描述一个模式，你就可以搜索它，并且可以操纵它。

在处理文本时，模式是很常见的。实际上，几个世纪以来文本模式已经成为一种组织概念。如果没有模式，我们的生活将会更艰难。在美国，以下的地址模式是很常见的：

     姓名
     街区编号 街区名称
     城市 州 邮政编码

一旦你理解了这种模式，你一眼就可以辨认出地址。你的心里习惯了每个数据元素的位置，因此不再需要标记出每个数据元素。你能够识别一个城市的名称并不是因为它被标记“城市名称”，而是因为它在模式中所处的位置。

识别出一个模式只是第一步。接下来要描述这个模式。然后，当你能够描述一个模式时，你就可以操纵它。如果给定一些符合上述模式的地址，你就可以提取出一个城市列表及其相应的邮政编码，并使用这个列表构造一个邮政编码目录。

识别模式取决于你。要描述你识别的模式，可以使用正则表达式。它们提供了一种简洁的符号，你可以用这些符号来描述文本数据可找到的任何模式。Oracle全新的正则表达式函数提供了操纵功能，使你能够搜索模式，提取符合模式的文本，以及将一种模式转换为另一种模式。

模式是模糊的。毫无疑问，你可以想到无数个前述地址模式的变形或例外。住在公寓中的人们将公寓号作为其地址的一部分。加拿大人没有“ZIP code”邮政编码，他们有“postal code”邮政编码，而这两种格式有显著的不同。在使用正则表达式时，了解你的数据并清楚可能破坏你正在使用的模式的变形是非常重要的。使用正则表达式的 很大一部分工作是了解你的数据并选择能够表示这些数据内在含义的表达式。

支持表达式

最新的Oracle数据库版本Oracle Database 10g以下面四种新函数的形式实施正则表达式支持，这些函数在SQL和PL/SQL中也同样适用：

REGEXP_LIKE

REGEXP_REPLACE

REGEXP_INSTR

REGEXP_SUBSTR

REGEXP_LIKE是一个布尔函数或SQL中的谓语，使你能够识别出一个列中包含符合给定模式的文本的行。它主要设计用于SELECT语句的WHERE子句中。

假设你有以下包含了关于密西根州立公园信息的表：


CREATE TABLE michigan_park (
    park_name VARCHAR2 (40),
    park_phone VARCHAR2 (15),
    description VARCHAR2 (500)

);


很多公园有多个电话号码，而且你发现令人有点恼火的是用户将这么多的电话号码记录在description（描述）字段中。你想要获得有这种情况的所有公 园的列表。由于美国本地电话号码一般遵循xxx-xxxx模式, 因此你决定使用REGEXP_LIKE来搜索公园描述中的电话号码。清单 1 显示了搜索结果。

以下是清单 1中对REGEXP_LIKE的调用：


WHERE REGEXP_LIKE(description,

  '...-....');


第一个参数是你想搜索的字符串，即description字段。第二个参数表示搜索的模式。在正则表达式中，句点(.)表示“任何字符”，所以这个表达式 代表任意三个字符接一个破折号再跟任意四个字符。无论何时当被搜索的文本中包含符合给定模式的文本时，该函数都会返回TRUE（真）。在这种情况下，模式 匹配可以发生在字段值中的任何地方。

清单 1 中的三点－四点模式和结果提供了一个很好的模糊分类的例子，这是你在使用正则表达式时必须接受的。首先，这种电话号码的模式在美国以外并没有广泛使用。我 在这里使用该模式只是因为我知道我的数据与作为美国一部分的密歇根州的公园相关，所以我认为这些公园会使用美国电话号码。 不过，请等一下！我的模式并没有完全成功。为什么Muskallonge 湖州立公园也在输出结果中？那个公园的描述中并没有电话号码。

Muskallonge 湖州立公园出现在清单1中是因为我选择了一个与我真正想要的结果松散匹配的模式。我没有指明我的模式中除破折号外必须由数字组成。你看到了描述中的字符串 “217-acre”吗？这就是为什么它会出现在结果中的原因：三个字符，一个破折号，再跟四个字符。REGEXP_LIKE给出了我所要求的结果。该函 数不知道数据的任何语义或意思。它并不知道我要找的是一个电话号码，它也不知道“217-acre”并不是电话号码。

清单 1中出现一个没有电话号码的公园描述会带来问题吗？这取决于我计划如何处理这些结果。如果我只是想知道电话号码，而且没有太多错误的匹配，那么我可以在阅 读报告时忽略那些不匹配的结果。但是，如果我计划以某种方式操纵这些电话号码，也许我就应该进一步改进我的模式。为此，我可以要求三个和四个数字而不是字 符，如下所示：


WHERE REGEXP_LIKE(description,
  '[0-9]{3}-[0-9]{4,4}');


到这里就可以结束了，但我没有这样做。看看清单 1中的数据，你会看到一些用户明显喜欢用句点来分隔电话号码的数字组。为了最大限度地提高查询效率，我应该允许在数字组之间使用破折号(-)或句点(.)，如下所示：


WHERE REGEXP_LIKE(description,
  '[0-9]{3}[-.][0-9]{4,4}');


让我们来解释一下这个表达式的每个部分：

[0-9]匹配任何数字

{3}重复前一个元素三次。

[-.] 匹配一个破折号或一个句点。

[0-9]匹配任何数字。

{4,4}前一个元素最少和最多都必须出现4次。

这看起来好象是个比较合理的严格表达式，因为我不希望有太多错误的匹配。清单 2显示了查询结果。然而，使用正则表达式来处理文本永远都是不精确的方法。如果某个描述为“见参考手册的第997-1012页”，那么这个描述也会与我建 立的表达式相匹配。正则表达式只匹配模式，它们只能匹配模式，不能辨别意思。

关于索引

标准的索引不能够用于正则表达式求值。然而，如果你有一个经常使用的正则表达式，你就可以建立一个函数索引来支持它。否则，如果只是你在一个查询的 WHERE子句中提供的谓词包含正则表达式，其结果将会是造成全表扫描。在查询大型表时要切记这一点。也许正则表达式对一个给定的查询来说是唯一可行的解 决方案，但是在对大型表进行全表扫描之前做出正确决策总是好的。

不要让正则表达式求值可能不会使用索引这个事实阻止你在查询中使用正则表达式。正则表达式实在是太有用了，千万不要把它弃于你的工具箱之外。如果一个正则 表达式正是你解决手头问题所需要的，那就是使用它。只是心里要清楚它的潜在开销。另外还要考虑到，在索引不相关的情况下正则表达式是非常有用的。例如，看 到描述字段中电话号码的多样性，我可能选择将park_phone字段限制为一个单一的格式，即在美国使用的区域代码和电话号码(xxx) xxx-xxxx模式，如清单 3所示。

清单 3中正则表达式两端的脱字符号(^)和美元符号($)是用于防止前导空格和尾部空格保存在park_phone字段中的锚点。脱字符号标志着表达式字段值的开始，而美元符号则标志着表达式的结束。这种标定可以避免前导或后缀空格。