REGEXPReplace使用方法.docx

REGEXP_REPLACE函数让我们首先看一下传统的REPLACESQL函数，它把一个字符串用另一个字符串来替换。假设您的数据在正文中有不必要的空格，您希望用单个空格来替换它们。利用REPLACE函数，您需要准确地列出您要替换多少个空格。然而，多余空格的数目在正文的各处可能不是相同的。下面的示例在Joe和Smith之间有三个空格。REPLACE函数的参数指定要用一个空格来替换两个空格。在这种情况下，结果在原来的字符串的Joe和Smith之间留下了一个额外的空格。SELECTREPLACE(JoeSmith,)ASreplaceFROMdualREPLACE-JoeSmithREGEXP_REPLACE函数把替换功能向前推进了一步，其语法在表9中列出。以下查询用单个空格替换了任意两个或更多的空格。()子表达式包含了单个空格，它可以按2,的指示重复两次或更多次。表9：REGEXP_REPLACE函数语法说明REGEXP_REPLACE(source_string,pattern,replace_string,position,occurrence,match_parameter)该函数用一个指定的replace_string来替换匹配的模式，从而允许复杂的搜索并替换操作。SELECTREGEXP_REPLACE(JoeSmith,()2,)ASRX_REPLACEFROMdualRX_REPLACE-JoeSmith后向引用正则表达式的一个有用的特性是能够存储子表达式供以后重用；这也被称为后向引用（在表10中对其进行了概述）。它允许复杂的替换功能，如在新的位置上交换模式或显示重复出现的单词或字母。子表达式的匹配部分保存在临时缓冲区中。缓冲区从左至右进行编号，并利用digit符号进行访问，其中digit是1到9之间的一个数字，它匹配第digit个子表达式，子表达式用一组圆括号来显示。接下来的例子显示了通过按编号引用各个子表达式将姓名EllenHildiSmith转变为Smith,EllenHildi。SELECTREGEXP_REPLACE(EllenHildiSmith,(.*)(.*)(.*),3,12)FROMdualREGEXP_REPLACE(EL-Smith,EllenHildi该SQL语句显示了用圆括号括住的三个单独的子表达式。每一个单独的子表达式包含一个匹配元字符(.)，并紧跟着*元字符，表示任何字符（除换行符之外）都必须匹配零次或更多次。空格将各个子表达式分开，空格也必须匹配。圆括号创建获取值的子表达式，并且可以用digit来引用。第一个子表达式被赋值为1，第二个2，以此类推。这些后向引用被用在这个函数的最后一个参数(3,12)中，这个函数有效地返回了替换子字符串，并按期望的格式来排列它们（包括逗号和空格）。表11详细说明了该正则表达式的各个组成部分。后向引用对替换、格式化和代替值非常有用，并且您可以用它们来查找相邻出现的值。接下来的例子显示了使用REGEP_SUBSTR函数来查找任意被空格隔开的重复出现的字母数字值。显示的结果给出了识别重复出现的单词is的子字符串。SELECTREGEXP_SUBSTR(Thefinaltestisistheimplementation,(:alnum:+)(:space:+)1)ASsubstrFROMdualSUBSTR-isis匹配参数选项您可能已经注意到了正则表达式操作符和函数包含一个可选的匹配参数。这个参数控制是否区分大小写、换行符的匹配和保留多行输入。正则表达式的实际应用您不仅可以在队列中使用正则表达式，还可以在使用SQL操作符或函数的任何地方（比如说在PL/SQL语言中）使用正则表达式。您可以编写利用正则表达式功能的触发器，以验证、生成或提取值。接下来的例子演示了您如何能够在一次列检查约束条件中应用REGEXP_LIKE操作符来进行数据验证。它在插入或更新时检验正确的社会保险号码格式。如123-45-6789和123456789之类格式的社会保险号码对于这种列约束条件是可接受的值。有效的数据必须以三个数字开始，紧跟着一个连字符，再加两个数字和一个连字符，最后又是四个数字。另一种表达式只允许9个连续的数字。竖线符号(|)将各个选项分开。ALTERTABLEstudentsADDCONSTRAINTstud_ssn_ckCHECK(REGEXP_LIKE(ssn,(:digit:3-:digit:2-:digit:4|:digit:9)$)由和$指示的开头或结尾的字符都是不可接受的。确保您的正则表达式没有分成多行或包含任何不必要的空格，除非您希望格式如此并相应地进行匹配。表12说明了该正则表达式示例的各个组成部分。将正则表达式与现有的功能进行比较正则表达式有几个优点优于常见的LIKE操作符和INSTR、SUBSTR及REPLACE函数的。这些传统的SQL函数不便于进行模式匹配。只有LIKE操作符通过使用%和_字符匹配，但LIKE不支持表达式的重复、复杂的更替、字符范围、字符列表和POSIX字符类等等。此外，新的正则表达式函数允许检测重复出现的单词和模式交换。这里的例子为您提供了正则表达式领域的一个概览，以及您如何能够在您的应用程序中使用它们。实实在在地丰富您的工具包因为正则表达式有助于解决复杂的问题，所以它们是非常强大的。正则表达式的一些功能难于用传统的SQL函数来仿效。当您了解了这种稍显神秘的语言的基础构建程序块时，正则表达式将成为您的工具包的不可缺少的一部分（不仅在SQL环境下也在其它的编程语言环境下）。为了使您的各个模式正确，虽然尝试和错误有时是必须的，但正则表达式的简洁和强大是不容置疑的。AliceRischert()是哥伦比亚大学计算机技术与应用系的数据库应用程序开发和设计方向的主席。她编写了OracleSQL交互手册第2版（PrenticeHall，2002）和即将推出的OracleSQL示例（PrenticeHall，2003）。Rischert拥有超过15年的经验在财富100强公司内担任数据库设计师、DBA和项目主管，并且她自从Oracleversion5起就一直使用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。）空白(第三个子表达式的开头tr.匹配除换行符之外的任意单字符*重复操作符，匹配之前的.元字符0到n次)第三个子表达式的结尾；匹配结果在3中获取（在这个例子中，结果为Smith。）表12：社会保险号码正则表达式的说明正则表达式项目说明行首字符（正则表达式在匹配之前不能有任何前导字符。）(开始子表达式并列出用|元字符分开的可替换选项字符列表的开头:digit:P