Oracle Database g 的一个新特性大大提高了您搜索和处理字符数据的能力这个特性就是正规表达式是一种用来描述文本模式的表示方法很久以来它已在许多编程语言和大量 UNIX 实用工具中出现过了
Oracle 的正规表达式的实施是以各种 SQL 函数和一个 WHERE 子句操作符的形式出现的如果您不熟悉正规表达式那么这篇文章可以让您了解一下这种新的极其强大然而表面上有点神秘的功能已经对正规表达式很熟悉的读者可以了解如何在 Oracle SQL 语言的环境中应用这种功能
什么是正规表达式？
正规表达式由一个或多个字符型文字和/或元字符组成在最简单的格式下正规表达式仅由字符文字组成如正规表达式 cat它被读作字母 c接着是字母 a 和 t这种模式匹配 catlocation 和 catalog 之类的字符串元字符提供算法来确定 Oracle 如何处理组成一个正规表达式的字符当您了解了各种元字符的含义时您将体会到正规表达式用于查找和替换特定的文本数据是非常强大的
验证数据识别重复关键字的出现检测不必要的空格或分析字符串只是正规表达式的许多应用中的一部分您可以用它们来验证电话号码邮政编码电子邮件地址社会安全号码IP 地址文件名和路径名等的格式此外您可以查找如 HTML 标记数字日期之类的模式或任意文本数据中符合任意模式的任何事物并用其它的模式来替换它们
用 Oracle Database g 使用正规表达式
您可以使用最新引进的 Oracle SQL REGEXP_LIKE 操作符和 REGEXP_INSTRREGEXP_SUBSTR 以及 REGEXP_REPLACE 函数来发挥正规表达式的作用您将体会到这个新的功能如何对 LIKE 操作符和 INSTRSUBSTR 和 REPLACE 函数进行了补充实际上它们类似于已有的操作符但现在增加了强大的模式匹配功能被搜索的数据可以是简单的字符串或是存储在数据库字符列中的大量文本正规表达式让您能够以一种您以前从未想过的方式来搜索替换和验证数据并提供高度的灵活性
正规表达式的基本例子
在使用这个新功能之前您需要了解一些元字符的含义句号 () 匹配一个正规表达式中的任意字符（除了换行符）例如正规表达式 ab 匹配的字符串中首先包含字母 a接着是其它任意单个字符（除了换行符）再接着是字母 b字符串 axbxaybx 和 abba 都与之匹配因为在字符串中隐藏了这种模式如果您想要精确地匹配以 a 开头和以 b 结尾的一条三个字母的字符串则您必须对正规表达式进行定位脱字符号 (^) 元字符指示一行的开始而美元符号 ($) 指示一行的结尾（参见表 ）因此 正规表达式 ^ab$ 匹配字符串 aababb 或 axb将这种方式与 LIKE ⊃Ù×÷·û提供的类似的模式匹配 a_b 相比较其中 (_) 是单字符通配符
默认情况下一个正规表达式中的一个单独的字符或字符列表只匹配一次为了指示在一个正规表达式中多次出现的一个字符您可以使用一个量词它也被称为重复操作符如果您想要得到从字母 a 开始并以字母 b 结束的匹配模式则您的正规表达式看起来像这样^a*b$* 元字符重复前面的元字符 () 指示的匹配零次一次或更多次LIKE 操作符的等价的模式是 a%b其中用百分号 (%) 来指示任意字符出现零次一次或多次
表 给出了重复操作符的完整列表注意它包含了特殊的重复选项它们实现了比现有的 LIKE 通配符更大的灵活性如果您用圆括号括住一个表达式这将有效地创建一个可以重复一定次数的子表达式例如正规表达式 b(an)*a 匹配 babanabananayourbananasplit 等
Oracle 的正规表达式实施支持 POSIX （可移植操作系统接口）字符类参见表 中列出的内容这意味着您要查找的字符类型可以非常特别假设您要编写一条仅查找非字母字符的 LIKE 条件 — 作为结果的 WHERE 子句可能不经意就会变得非常复杂
POSIX 字符类必须包含在一个由方括号 ([]) 指示的字符列表中例如正规表达式 [[:lower:]] 匹配一个小写字母字符而 [[:lower:]]{} 匹配五个连续的小写字母字符
除 POSIX 字符类之外您可以将单独的字符放在一个字符列表中例如正规表达式 ^ab[cd]ef$ 匹配字符串 abcef 和 abdef必须选择 c 或 d
除脱字符 (^) 和连字符 () 之外字符列表中的大多数元字符被认为是文字正规表达式看起来很复杂这是因为一些元字符具有随上下文环境而定的多重含义^ 就是这样一种元字符如果您用它作为一个字符列表的第一个字符它代表一个字符列表的非因此[^[:digit:]] 查找包含了任意非数字字符的模式而 ^[[:digit:]] 查找以数字开始的匹配模式连字符 () 指示一个范围正规表达式 [am] 匹配字母 a 到字母 m 之间的任意字母但如果它是一个字符行中的第一个字符（如在 [afg] 中）则它就代表连字符
之前的一个例子介绍了使用圆括号来创建一个子表达式它们允许您通过输入更替元字符来输入可更替的选项这些元字符由竖线 (|) 分开
例如正规表达式 t(a|e|i)n 允许字母 t 和 n 之间的三种可能的字符更替匹配模式包括如 tantentin 和 Pakistan 之类的字但不包括 teenmountain 或 tune作为另一种选择正规表达式 t(a|e|i)n 也可以表示为一个字符列表 t[aei]n表 汇总了这些元字符虽然存在更多的元字符但这个简明的概述足够用来理解这篇文章使用的正规表达式
REGEXP_LIKE 操作符
REGEXP_LIKE 操作符向您介绍在 Oracle 数据库中使用时的正规表达式功能表 列出了 REGEXP_LIKE 的语法
下面的 SQL 查询的 WHERE 子句显示了 REGEXP_LIKE 操作符它在 ZIP 列中搜索满足正规表达式 [^[:digit:]] 的模式它将检索 ZIPCODE 表中的那些 ZIP 列值包含了任意非数字字符的行
SELECT zip
FROM zipcode
WHERE REGEXP_LIKE(zip [^[:digit:]])
ZIP
ab
xy
ab
abcxy
这个正规表达式的例子仅由元字符组成更具体来讲是被冒号和方括号分隔的 POSIX 字符类 digit第二组方括号（如 [^[:digit:]] 中所示）包括了一个字符类列表如前文所述需要这样做是因为您只可以将 POSIX 字符类用于构建一个字符列表
REGEXP_INSTR 函数
这个函数返回一个模式的起始位置因此它的功能非常类似于 INSTR 函数新的 REGEXP_INSTR 函数的语法在表 中给出这两个函数之间的主要区别是REGEXP_INSTR 让您指定一种模式而不是一个特定的搜索字符串因而它提供了更多的功能接下来的示例使用 REGEXP_INSTR 来返回字符串 Joe Smith Berry Lane San Joseph CA 中的五位邮政编码模式的起始位置如果正规表达式被写为 [[:digit:]]{}则您将得到门牌号的起始位置而不是邮政编码的因为 是第一次出现五个连续数字因此您必须将表达式定位到该行的末尾正如 $ 元字符所示该函数将显示邮政编码的起始位置而不管门牌号的数字个数
SELECT REGEXP_INSTR(Joe Smith Berry Lane San Joseph CA
[[:digit:]]{}$)
AS rx_instr
FROM dual
RX_INSTR
编写更复杂的模式
让我们在前一个例子的邮政编码模式上展开以便包含一个可选的四位数字模式您的模式现在可能看起来像这样[[:digit:]]{}([[:digit:]]{})?$如果您的源字符串以 位邮政编码或 位 + 位邮政编码的格式结束则您将能够显示该模式的起始位置
SELECT REGEXP_INSTR(Joe Smith Berry Lane San Joseph CA
[[:digit:]]{}([[:digit:]]{})?$)
AS starts_at
FROM dual
STARTS_AT
在这个示例中括弧里的子表达式 ([[:digit:]]{}) 将按 ? 重复操作符的指示重复零次或一次此外企图用传统的 SQL 函数来实现相同的结果甚至对 SQL 专家也是一个挑战为了更好地说明这个正规表达式示例的不同组成部分表 包含了一个对单个文字和元字符的描述
REGEXP_SUBSTR 函数
·Ç⊃£ÀàËÆÓÚ SUBSTR 函数的 REGEXP_SUBSTR 函数用来提取一个字符串的一部分表 显示了这个新函数的语法在下面的示例中匹配模式 [^]* 的字符串将被返回该正规表达式搜索其后紧跟着空格的一个逗号然后按 [^]* 的指示搜索零个或更多个不是逗号的字符最后查找另一个逗号这种模式看起来有点像一个用逗号分隔的值字符串
SELECT REGEXP_SUBSTR(first field second field third field
[^]*)
FROM dual
REGEXP_SUBSTR(FIR
second field
REGEXP_REPLACE 函数
让我们首先看一下传统的 REPLACE SQL 函数它把一个字符串用另一个字符串来替换假设您的数据在正文中有不必要的空格您希望用单个空格来替换它们利用 REPLACE 函数您需要准确地列出您要替换多少个空格然而多余空格的数目在正文的各处可能不是相同的下面的示例在 Joe 和 Smith 之间有三个空格REPLACE 函数的参数指定要用一个空格来替换两个空格在这种情况下结果在原来的字符串的 Joe 和 Smith 之间留下了一个额外的空格
SELEC
