行使正规表达式编写更好的 SQL
发布时间：2018-09-02 18:55:22 所属栏目：电商 来源：站长网
导读：Oracle Database 10g 中的正规表达式特征是一个用于处理赏罚文本数据的强盛器材 Oracle Database 10g 的一个新特征大大进步了您搜刮和处理赏罚字符数据的手段。这个特征就是正规表达式，是一种用来描写文本模式的暗示要领。好久以来它已在很多编程说话和大量 UNIX
|
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）。因此， 正规表达式 ^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 (ar280@yahoo.com) 是哥伦比亚大学计较机技能与应用系的数据库应用措施开拓和计划偏向的主席。她编写了 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 次 )竣事圆括号，竣事用于替代的子表达式组$定位元字符，指示行尾；没有特另外字符可以或许切合模式 （编辑：河北网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
热点阅读