使用正则表达式编写sql

Oracle Database 10g 中的正规表达式特性是一个用于处理文本数据的强大工具 　　Oracle Database 10g 的一个新特性大大提高了您搜索和处理字符数据的能力。这个特性就是正规表达式，是一种用来描述文本模式的表示方法。很久以来它已在许多编程语言和大量 UNIX 实用工具中出现过了。 　　Oracle 的正规表达式的实施是以各种 SQL 函数和一个 WHERE 子句操作符的形式出现的。如果您不熟悉正规表达式，那么这篇文章可以让您了解一下这种新的极其强大然而表面上有点神秘的功能。已经对正规表达式很熟悉的读者可以了解如何在 Oracle SQL 语言的环境中应用这种功能。 什么是正规表达式？ 　　正规表达式由一个或多个字符型文字和/或元字符组成。在最简单的格式下，正规表达式仅由字符文字组成，如正规表达式 cat。它被读作字母 c，接着是字母 a 和 t，这种模式匹配 cat、location 和 catalog 之类的字符串。元字符提供算法来确定 Oracle 如何处理组成一个正规表达式的字符。当您了解了各种元字符的含义时，您将体会到正规表达式用于查找和替换特定的文本数据是非常强大的。 　　验证数据、识别重复关键字的出现、检测不必要的空格，或分析字符串只是正规表达式的许多应用中的一部分。您可以用它们来验证电话号码、邮政编码、电子邮件地址、社会安全号码、IP 地址、文件名和路径名等的格式。此外，您可以查找如 HTML 标记、数字、日期之类的模式，或任意文本数据中符合任意模式的任何事物，并用其它的模式来替换它们。 用 Oracle Database 10g 使用正规表达式 　　您可以使用最新引进的 Oracle SQL REGEXP_LIKE 操作符和 REGEXP_INSTR、REGEXP_SUBSTR 以及 REGEXP_REPLACE 函数来发挥正规表达式的作用。您将体会到这个新的功能如何对 LIKE 操作符和 INSTR、SUBSTR 和 REPLACE 函数进行了补充。实际上，它们类似于已有的操作符，但现在增加了强大的模式匹配功能。被搜索的数据可以是简单的字符串或是存储在数据库字符列中的大量文本。正规表达式让您能够以一种您以前从未想过的方式来搜索、替换和验证数据，并提供高度的灵活性。 正规表达式的基本例子 　　在使用这个新功能之前，您需要了解一些元字符的含义。句号 (.) 匹配一个正规表达式中的任意字符（除了换行符）。例如，正规表达式 a.b 匹配的字符串中首先包含字母 a，接着是其它任意单个字符（除了换行符），再接着是字母 b。字符串 axb、xaybx 和 abba 都与之匹配，因为在字符串中隐藏了这种模式。如果您想要精确地匹配以 a 开头和以 b 结尾的一条三个字母的字符串，则您必须对正规表达式进行定位。脱字符号 (^) 元字符指示一行的开始，而美元符号 ($) 指示一行的结尾（参见表1：附表见第4页）。因此， 正规表达式 ^a.b$ 匹配字符串 aab、abb 或 axb。将这种方式与 LIKE 操作符提供的类似的模式匹配 a_b 相比较，其中 (_) 是单字符通配符。 　　默认情况下，一个正规表达式中的一个单独的字符或字符列表只匹配一次。为了指示在一个正规表达式中多次出现的一个字符，您可以使用一个量词，它也被称为重复操作符。.如果您想要得到从字母 a 开始并以字母 b 结束的匹配模式，则您的正规表达式看起来像这样：^a.*b$。* 元字符重复前面的元字符 (.) 指示的匹配零次、一次或更多次。LIKE 操作符的等价的模式是 a%b，其中用百分号 (%) 来指示任意字符出现零次、一次或多次。 　　表 2 给出了重复操作符的完整列表。注意它包含了特殊的重复选项，它们实现了比现有的 LIKE 通配符更大的灵活性。如果您用圆括号括住一个表达式，这将有效地创建一个可以重复一定次数的子表达式。例如，正规表达式 b(an)*a 匹配 ba、bana、banana、yourbananasplit 等。 　　Oracle 的正规表达式实施支持 POSIX （可移植操作系统接口）字符类，参见表 3 中列出的内容。这意味着您要查找的字符类型可以非常特别。假设您要编写一条仅查找非字母字符的 LIKE 条件 — 作为结果的 WHERE 子句可能不经意就会变得非常复杂。 　　POSIX 字符类必须包含在一个由方括号 ([]) 指示的字符列表中。例如，正规表达式 [[:lower:]] 匹配一个小写字母字符，而 [[:lower:]]{5} 匹配五个连续的小写字母字符。 　　除 POSIX 字符类之外，您可以将单独的字符放在一个字符列表中。例如，正规表达式 ^ab[cd]ef$ 匹配字符串 abcef 和 abdef。必须选择 c 或 d。 　　除脱字符 (^) 和连字符 (-) 之外，字符列表中的大多数元字符被认为是文字。正规表达式看起来很复杂，这是因为一些元字符具有随上下文环境而定的多重含义。^ 就是这样一种元字符。如果您用它作为一个字符列表的第一个字符，它代表一个字符列表的非。因此，[^[:digit:]] 查找包含了任意非数字字符的模式，而 ^[[:digit:]] 查找以数字开始的匹配模式。连字符 (-) 指示一个范围，正规表达式 [a-m] 匹配字母 a 到字母 m 之间的任意字母。但如果它是一个字符行中的第一个字符（如在 [-afg] 中），则它就代表连字符。 　　之前的一个例子介绍了使用圆括号来创建一个子表达式；它们允许您通过输入更替元字符来输入可更替的选项，这些元字符由竖线 (|) 分开。 　　例如，正规表达式 t(a|e|i)n 允许字母 t 和 n 之间的三种可能的字符更替。匹配模式包括如 tan、ten、tin 和 Pakistan 之类的字，但不包括 teen、mountain 或 tune。作为另一种选择，正规表达式 t(a|e|i)n 也可以表示为一个字符列表 t[aei]n。表 4 汇总了这些元字符。虽然存在更多的元字符，但这个简明的概述足够用来理解这篇文章使用的正规表达式。 REGEXP_LIKE 操作符 　　REGEXP_LIKE 操作符向您介绍在 Oracle 数据库中使用时的正规表达式功能。表 5 列出了 REGEXP_LIKE 的语法。 　　下面的 SQL 查询的 WHERE 子句显示了 REGEXP_LIKE 操作符，它在 ZIP 列中搜索满足正规表达式 [^[:digit:]] 的模式。它将检索 ZIPCODE 表中的那些 ZIP 列值包含了任意非数字字符的行。 SELECT zip FROM zipcode WHERE REGEXP_LIKE(zip, '[^[:digit:]]') ZIP ----- ab123 123xy 007ab abcxy 　　这个正规表达式的例子仅由元字符组成，更具体来讲是被冒号和方括号分隔的 POSIX 字符类 digit。第二组方括号（如 [^[:digit:]] 中所示）包括了一个字符类列表。如前文所述，需要这样做是因为您只可以将 POSIX 字符类用于构建一个字符列表。 REGEXP_INSTR 函数 　　这个函数返回一个模式的起始位置，因此它的功能非常类似于 INSTR 函数。新的 REGEXP_INSTR 函数的语法在表 6 中给出。这两个函数之间的主要区别是，REGEXP_INSTR 让您指定一种模式，而不是一个特定的搜索字符串；因而它提供了更多的功能。接下来的示例使用 REGEXP_INSTR 来返回字符串 Joe Smith, 10045 Berry Lane, San Joseph, CA 91234 中的五位邮政编码模式的起始位置。如果正规表达式被写为 [[:digit:]]{5}，则您将得到门牌号的起始位置而不是邮政编码的，因为 10045 是第一次出现五个连续数字。因此，您必须将表达式定位到该行的末尾，正如 $ 元字符所示，该函数将显示邮政编码的起始位置，而不管门牌号的数字个数。 SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234', '[[:digit:]]{5}$') AS rx_instr FROM dual RX_INSTR ---------- 45 编写更复杂的模式 　　让我们在前一个例子的邮政编码模式上展开，以便包含一个可选的四位数字模式。您的模式现在可能看起来像这样：[[:digit:]]{5}(-[[:digit:]]{4})?$。如果您的源字符串以 5 位邮政编码或 5 位 + 4 位邮政编码的格式结束，则您将能够显示该模式的起始位置。 SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234', ' [[:digit:]]{5}(-[[:digit:]]{4})?$') AS starts_at FROM dual STARTS_AT ---------- 44 　　在这个示例中，括弧里的子表达式 (-[[:digit:]]{4}) 将按 ? 重复操作符的指示重复零次或一次。此外，企图用传统的 SQL 函数来实现相同的结果甚至对 SQL 专家也是一个挑战。为了更好地说明这个正规表达式示例的不同组成部分，表 7 包含了一个对单个文字和元字符的描述。 REGEXP_SUBSTR 函数 　　类似于 SUBSTR 函数的 REGEXP_SUBSTR 函数用来提取一个字符串的一部分。表 8 显示了这个新函数的语法。在下面的示例中，匹配模式 [^,]* 的字符串将被返回。该正规表达式搜索其后紧跟着空格的一个逗号；然后按 [^,]* 的指示搜索零个或更多个不是逗号的字符，最后查找另一个逗号。这种模式看起来有点像一个用逗号分隔的值字符串。 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 之间留下了一个额外的空格。 SELECT REPLACE('Joe Smith',' ', ' ') AS replace FROM dual REPLACE --------- Joe Smith 　　REGEXP_REPLACE 函数把替换功能向前推进了一步，其语法在表 9 中列出。以下查询用单个空格替换了任意两个或更多的空格。( ) 子表达式包含了单个空格，它可以按 {2,} 的指示重复两次或更多次。 SELECT REGEXP_REPLACE('Joe Smith', '( ){2,}', ' ') AS RX_REPLACE FROM dual RX_REPLACE ---------- Joe Smith 后向引用 　　正则表达式的一个有用的特性是能够存储子表达式供以后重用；这也被称为后向引用（在表 10 中对其进行了概述）。它允许复杂的替换功能，如在新的位置上交换模式或显示重复出现的单词或字母。子表达式的匹配部分保存在临时缓冲区中。缓冲区从左至右进行编号，并利用 \digit 符号进行访问，其中 digit 是 1 到 9 之间的一个数字，它匹配第 digit 个子表达式，子表达式用一组圆括号来显示。 　　接下来的例子显示了通过按编号引用各个子表达式将姓名 Ellen Hildi Smith 转变为 Smith, Ellen Hildi。 SELECT REGEXP_REPLACE( 'Ellen Hildi Smith', '(.*) (.*) (.*)', '\3, \1 \2') FROM dual REGEXP_REPLACE('EL ------------------ Smith, Ellen Hildi 　　该 SQL 语句显示了用圆括号括住的三个单独的子表达式。每一个单独的子表达式包含一个匹配元字符 (.)，并紧跟着 * 元字符，表示任何字符（除换行符之外）都必须匹配零次或更多次。空格将各个子表达式分开，空格也必须匹配。圆括号创建获取值的子表达式，并且可以用 \digit 来引用。第一个子表达式被赋值为 \1 ，第二个 \2，以此类推。这些后向引用被用在这个函数的最后一个参数 (\3, \1 \2) 中，这个函数有效地返回了替换子字符串，并按期望的格式来排列它们（包括逗号和空格）。表 11 详细说明了该正则表达式的各个组成部分。 　　后向引用对替换、格式化和代替值非常有用，并且您可以用它们来查找相邻出现的值。接下来的例子显示了使用 REGEP_SUBSTR 函数来查找任意被空格隔开的重复出现的字母数字值。显示的结果给出了识别重复出现的单词 is 的子字符串。 SELECT REGEXP_SUBSTR( 'The final test is is the implementation', '([[:alnum:]]+)([[:space:]]+)\1') AS substr FROM dual SUBSTR ------ is is 匹配参数选项 　　您可能已经注意到了正则表达式操作符和函数包含一个可选的匹配参数。这个参数控制是否区分大小写、换行符的匹配和保留多行输入。 正则表达式的实际应用 　　您不仅可以在队列中使用正则表达式，还可以在使用 SQL 操作符或函数的任何地方（比如说在 PL/SQL 语言中）使用正则表达式。您可以编写利用正则表达式功能的触发器，以验证、生成或提取值。 　　接下来的例子演示了您如何能够在一次列检查约束条件中应用 REGEXP_LIKE 操作符来进行数据验证。它在插入或更新时检验正确的社会保险号码格式。如 123-45-6789 和 123456789 之类格式的社会保险号码对于这种列约束条件是可接受的值。有效的数据必须以三个数字开始，紧跟着一个连字符，再加两个数字和一个连字符，最后又是四个数字。另一种表达式只允许 9 个连续的数字。竖线符号 (|) 将各个选项分开。 ALTER TABLE students ADD CONSTRAINT stud_ssn_ck CHECK (REGEXP_LIKE(ssn, '^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$')) 　　由 ^ 和 $ 指示的开头或结尾的字符都是不可接受的。确保您的正则表达式没有分成多行或包含任何不必要的空格，除非您希望格式如此并相应地进行匹配。表 12 说明了该正则表达式示例的各个组成部分。 将正则表达式与现有的功能进行比较 　　正则表达式有几个优点优于常见的 LIKE 操作符和 INSTR、SUBSTR 及 REPLACE 函数的。这些传统的 SQL 函数不便于进行模式匹配。只有 LIKE 操作符通过使用 % 和 _ 字符匹配，但 LIKE 不支持表达式的重复、复杂的更替、字符范围、字符列表和 POSIX 字符类等等。此外，新的正则表达式函数允许检测重复出现的单词和模式交换。这里的例子为您提供了正则表达式领域的一个概览，以及您如何能够在您的应用程序中使用它们。 实实在在地丰富您的工具包 　　因为正则表达式有助于解决复杂的问题，所以它们是非常强大的。正则表达式的一些功能难于用传统的 SQL 函数来仿效。当您了解了这种稍显神秘的语言的基础构建程序块时，正则表达式将成为您的工具包的不可缺少的一部分（不仅在 SQL 环境下也在其它的编程语言环境下）。为了使您的各个模式正确，虽然尝试和错误有时是必须的，但正则表达式的简洁和强大是不容置疑的。 　　Alice Rischert ([e-mail]) 是哥伦比亚大学计算机技术与应用系的数据库应用程序开发和设计方向的。她编写了 Oracle SQL 交互手册 第 2 版（Prentice Hall，2002）和即将推出的 Oracle SQL 示例 （Prentice Hall，2003）。Rischert 拥有超过 15 年的经验在财富 100 强公司内担任数据库设计师、DBA 和项目主管，并且她自从 Oracle version 5 起就一直使用 Oracle 产品。 　　附表见下页： 表 1：定位元字符 元字符 说明 ^ 使表达式定位至一行的开头 $ 使表达式定位至一行的末尾 表 2：量词或重复操作符量词 说明 * 匹配 0 次或更多次 ? 匹配 0 次或 1 次 + 匹配 1 次或更多次 {m} 正好匹配m次 {m,} 至少匹配m次 {m, n} 至少匹配m次但不超过n次 表 3：预定义的 POSIX 字符类字符类 说明 [:alpha:] 字母字符 [:lower:] 小写字母字符 [:upper:] 大写字母字符 [:digit:] 数字 [:alnum:] 字母数字字符 [:space:] 空白字符（禁止打印），如回车符、换行符、竖直制表符和换页符 [:punct:] 标点字符 [:cntrl:] 控制字符（禁止打印） [:print:] 可打印字符 表 4：表达式的替换匹配和分组元字符 说明 | 替换 分隔替换选项，通常与分组操作符()一起使用 ( ) 分组 将子表达式分组为一个替换单元、量词单元或后向引用单元（参见“后向引用”部分） [char] 字符列表 表示一个字符列表；一个字符列表中的大多数元字符（除字符类、^和-元字符之外）被理解为文字 表 5：REGEXP_LIKE操作符语法 说明 REGEXP_LIKE(source_string, pattern [, match_parameter]) source_string支持字符数据类型（CHAR、VARCHAR2、CLOB、NCHAR、NVARCHAR2和NCLOB，但不包括LONG）。pattern参数是正则表达式的另一个名称。match_parameter允许可选的参数（如处理换行符、保留多行格式化以及提供对区分大小写的控制）。 表 6：REGEXP_INSTR函数语法 说明 REGEXP_INSTR(source_string, pattern [, start_position [, occurrence [, return_option [, match_parameter]]]]) 该函数查找pattern，并返回该模式的第一个位置。您可以随意指定您想要开始搜索的start_position。occurrence参数默认为 1，除非您指定您要查找接下来出现的一个模式。return_option的默认值为 0，它返回该模式的起始位置；值为 1 则返回符合匹配条件的下一个字符的起始位置。 表 7： 5 位数字加 4 位邮政编码表达式的说明语法 说明 必须匹配的空白 [:digit:] POSIX 数字类 ] 字符列表的结尾 {5} 字符列表正好重复出现 5 次 ( 子表达式的开头 - 一个文字连字符，因为它不是一个字符列表内的范围元字符 [ 字符列表的开头 [:digit:] POSIX[:digit:]类 [ 字符列表的开头 ] 字符列表的结尾 {4} 字符列表正好重复出现 4 次 ) 结束圆括号，结束子表达式 ? ?量词匹配分组的子表达式 0 或 1 次，从而使得 4 位代码可选 $ 定位元字符，指示行尾 表 8：REGEXP_SUBSTR函数语法 说明 REGEXP_SUBSTR(source_string, pattern [, position [, occurrence [, match_parameter]]]) REGEXP_SUBSTR函数返回匹配模式的子字符串。 表 9：REGEXP_REPLACE函数语法 说明 REGEXP_REPLACE(source_string, pattern [, replace_string [, position [,occurrence, [match_parameter]]]]) 该函数用一个指定的replace_string来替换匹配的模式，从而允许复杂的“搜索并替换”操作。 表 10：后向引用元字符元字符 说明 \digit 反斜线 紧跟着一个 1 到 9 之间的数字，反斜线匹配之前的用括号括起来的第 digit 个子表达式。 （注意：反斜线在正则表达式中有另一种意义，取决于上下文，它还可能表示 Escape 字符。 表 11：模式交换正则表达式的说明正则表达式项目 说明 ( 第一个子表达式的开头 . 匹配除换行符之外的任意单字符 * 重复操作符，匹配之前的.元字符 0 到n次 ) 第一个子表达式的结尾；匹配结果在\1 中获取（在这个例子中，结果为Ellen。） 必须存在的空白 ( 第二个子表达式的开头 . 匹配除换行符之外的任意单个字符 * 重复操作符，匹配之前的.元字符 0 到n次 ) 第二个子表达式的结尾；匹配结果在\2 中获取（在这个例子中，结果为Hildi。） 空白 ( 第三个子表达式的开头 . 匹配除换行符之外的任意单字符 * 重复操作符，匹配之前的.元字符 0 到n次 ) 第三个子表达式的结尾；匹配结果在\3 中获取（在这个例子中，结果为Smith。） 表 12：社会保险号码正则表达式的说明正则表达式项目 说明 ^ 行首字符（正则表达式在匹配之前不能有任何前导字符。） ( 开始子表达式并列出用 | 元字符分开的可替换选项 [ 字符列表的开头 [:digit:] POSIX 数字类 ] 字符列表的结尾 {3} 字符列表正好重复出现 3 次 - 连字符 [ 字符列表的开头 [:digit:] POSIX 数字类 ] 字符列表的结尾 {2} 字符列表正好重复出现 2 次 - 另一个连字符 [ 字符列表的开头 [:digit:] POSIX 数字类 ] 字符列表的结尾 {4} 字符列表正好重复出现 4 次 | 替换元字符；结束第一个选项并开始下一个替换表达式 [ 字符列表的开头 [:digit:] POSIX 数字类 ] 字符列表的结尾 {9} 字符列表正好重复出现 9 次 ) 结束圆括号，结束用于替换的子表达式组 $ 定位元字符，指示行尾；没有额外的字符能够符合模式