Oracle正则表达式注意的各方面的问题.doc

资料由北京户口： 北京代孕： 提供Oracle正则表达式中注意的问题在Oracle11g中，当在正则表达式中用了中括号时，某些符号会匹配不了，如sd，要用类似于:digit:blank:才能匹配成功，以下是我测试的例子：1 -中括号中的内容为一杠及空格（- ），匹配成功，结果为:SA01-2345 6789 0123 4567 89002 SELECT to_char(REGEXP_SUBSTR(SA01-2345 6789 0123 4567 8900,3 SAd2- d4- d4- d4- d4- d4|SAd22,41, 1,i) as Reg_Str FROM dual;56 -中括号中的内容为（-:blank:），匹配成功，结果为:SA01-2345 6789 0123 4567 89007 SELECT to_char(REGEXP_SUBSTR(SA01-2345 6789 0123 4567 8900,8 SAd2-:blank:d4-:blank:d4-:blank:d4-:blank:d4-:blank:d4|SAd22,91, 1,i) as Reg_Str FROM dual;1011 -如果将中括号换成小括号，匹配成功，结果为:SA01-2345 6789 0123 4567 890012 SELECT to_char(REGEXP_SUBSTR(SA01-2345 6789 0123 4567 8900,13 (SA)?d2(s|-)d4(s|-)d4(s|-)d4(s|-)d4(s|-)d4|(SA)?d22,141, 1,i) as Reg_Str FROM dual;1516 -中括号中的内容为（-:digit:blank:），匹配成功，结果为:SA01-2345 6789 0123 4567 890017 SELECT to_char(REGEXP_SUBSTR(SA01-2345 6789 0123 4567 8900,18 (SA)?-:digit:blank:22,50,191, 1,i) as Reg_Str FROM dual;2021 -中括号中的内容为（-d:blank:），匹配失败，结果为空22 SELECT to_char(REGEXP_SUBSTR(SA01-2345 6789 0123 4567 8900,23 (SA)?-d:blank:22,50,241, 1,i) as Reg_Str FROM dual;2526 -中括号内容如下，匹配失败，结果为空27 SELECT to_char(REGEXP_SUBSTR(SA01-2345 6789 0123 4567 8900,28 (SA)?-ds22,50,291, 1,i) as Reg_Str FROM dual;3031 -中括号内容如下，匹配失败，结果为空32 SELECT to_char(REGEXP_SUBSTR(SA01-2345 6789 0123 4567 8900,33 (SA)?-d 22,50,341, 1,i) as Reg_Str FROM dual;3536 -中括号中的内容为（-s），匹配失败，结果为空37 SELECT to_char(REGEXP_SUBSTR(SA01-2345 6789 0123 4567 8900,38 SAd2-sd4-sd4-sd4-sd4-sd4|SAd22,391, 1,i) as Reg_Str FROM dual;明白原因的麻烦在下面评论，Oracle 8 和Oracle 9i中缺乏灵活性的SQL 正则表达式最终在Oracle 10g中得到了解决。Oracle 数据库目前内建了符合POSIX 标准的正则表达式。四个新的函数分别是：REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和EGEXP_REPLACE。它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法，但是它们使用POSIX 正则表达式代替了老的百分号（%）和通配符（_）字符。POSIX 正则表达式由标准的元字符（metacharacters）所构成： 表示字符串的开始$ 表示字符串的结束. 表示任何字符字符的范围，比如说a-z，表示任何ASCII 小写字母，与字符类:lower: 等价? 允许一个后继字符匹配零次或一次 允许一个后继字符匹配一次或多次* 表示零次或多次可以使用m,n 指定一个精确地出现范围，其意思是出现从m 次到n 次；m 表示正好m次；而m, 表示至少m次。还可以使用圆括号组合字符的集合，使用|（竖线）表示可替换。例如，字符串(a-z |0-9 )$将匹配所有由小写字母或数字组合成的字符串。REGEXP_LIKE 与LIKE 操作符相似。如果第一个参数匹配正则表达式它就解析为TRUE。例如Where REGEXP_LIKE(ENAME,JAO,i) 将在ENAME 以JA 或JO 开始的情况下返回一行数据。I 参数指定正则表达式是大小写敏感的。另外还可以在CHECK 约束和函数索引中指定REGEXP_LIKE。例如：Alter TABLE EMP ADD CONSTRAINT REGEX01CHECK (REGEXP_LIKE(ENAME,:alpha: $);这条语句使得ENAME 字段只能包含字母和数字字符（也就是说没有空格或者标点符号）。试图插入或者更新这些数据将导致一个ORA-2290 异常，或者检查约束的有效性。REGEXP_INSTR 与INSTR 函数类似。它返回一个字符串中匹配一个正则表达式的第一个子串的开始位置。例如：Select REGEXP_INSTR(The total is $400 for your purchase.,$:digit: )FROM DUAL;这个查询返回14，即$400在字符串的开始位置。另外还可以指定子串出现的次数；开始搜索的位置；是返回匹配的位置还是返回匹配之后字符的位置。REGEXP_SUBSTR 返回匹配一个正则表达式的子串。虽然结合使用SUBSTR 和REGEXP_INSTR 及LENGTH 也可以实现这一功能，但是使用这个函数却更为简单。Select REGEXP_INSTR(one,two,three,*) FROM DUAL;这个查询返回one，将第一个参数看成一个逗号分隔的列表并返回第一个逗号之前的所有字符。REGEXP_REPLACE 返回初始参数被匹配子串替换之后的结果。例如：Select REGEXP_REPLACE(The temperature is 23F,(:digit:) F,(1-32)*5/9|C)FROM DUAL;这个查询将查找一个华氏温度并将其转换为摄氏度。它将返回：The temperature is -5C。下面是其官方文档：Writing Better SQL Using Regular ExpressionsBy Alice Rischert10g 的正则表达式是处理文本数据的一个强有力的工具, 正则表达式在许多程序语言和Unix下已经使用很久了.oracle 通过在SQL函数或者Where子句中执行正则表达式, 如果你对正则表达式不熟悉，这篇文章可以给你一个大概的介绍, 熟悉正则表达式的读者可以了解怎么在Oracle SQL语言中实现这个功能什么是正则表达式？正则表达式由一个或多个字符和/或者字符元， 最简单的形式由字符组成， 像正则表达式cat,被解释为字符c后面跟着a和t, 这样的模式匹配字符串有 cat, location 和catalog. 字符元为Oracle怎么处理正则表达式提供了运算规则。 如果, 你了解了各种字符元的意思, 你就明白正则表达式在处理独立和代替文本字符方面是多么的强大.数据有效性， 重复词的辨认, 无关的空白检测，或者分解多个正则组成的字符串， 可以用来检查电话号码的有效性， 邮政编码，E-mail地址， 社保号码，IP地址， 文件名和路径， 等等。甚至可以用来模式定位,如HTML的标签，数字， 日期，或者任何模式匹配的字符或者代替他们的模式在Oracle 10g中使用正则表达式为了控制正则表达式可以使用Oracle中新的REGEXP_LIKE, REGEXP_INSTR, 和REG_EXP_REPLACE 函数， 你将会看到这些函数怎样增强了LIKE, INSTR, SUBSTR和REPLACE函数的功能. 实际上，和已经存在的函数呵操作相似，只是提供了强大的字符匹配能力。正则表达式简单的例子使用新功能之前， 要了解一些字符元的含义。句号(.)匹配正则表达式中的任一个字符(不包括新的一行). 例如正则表达式a.b匹配一个包括a， 然后任意一个字符， 然后是b的字符串， 如字符串axb, xaybx, abba。 如果要匹配以a开头以b结尾的三个字符的字符串, 必须使用字符元指定开始行, 以$符号结束， 因此, 正规表达式a.b$匹配的字符串如: aab, abb 或者axb, 就像LIKE 操作一样 a_b一样。默认的, 单个字符或者字符串列表再正则表达式中只匹配一次，如过要多次匹配，必须使用循环操作。 如果你想匹配一个以a开头， 以b结尾的字符串， 正则表达式为：a.*b$, *字符元重复.0次，1次或者多次。表2列出了完全的重复操作。 如果在表达式重使用圆括号, 可以创建子表达式，被重复执行数次。 例如, 正则表达式b(an)*a 可以匹配 ba, bana, banana, yourbananasplit, 等等oracle正则表达式支持POSIX字符类，见表3。 那意味着你可以很精确的使用你想要的字符类型。 想象一下为一个不是按照字母顺序的纪录写一个LIKE条件的查询， Where条件很容易变得非常复杂POSIX字符类必须以方括号结束(), 例如正则表达式匹配一个小写字符, 5匹配5个连续的小写字符除了POSIX字符类以外， 还可以在字符列中放置单独的字符， 例如正则表达式abcdef$匹配字符串abcef和abdef, 字符c或者d必须被选中.大部分字符串列表中的字符元从字面上解释, 除了字符和-. 正则表达式看起来很复杂是由于有些字符元有多种意思， 这取决于上下文, 就是这样的一个字符元. 如果是字符列中的第一个字符，就表示对这个字符串取反，因此, 就是表示查找不包含数字的模式, 匹配以数字开头的字符串.字符-表示一个范围，正则表达式a-m匹配从a到m的字母, 但是如果字符-在开头，就表示一个连字符，如-afg.前面的一个例子介绍了用圆括号创建子表达式, 可以用竖线(|)进入循环交替. 例如, 正则表达式t(a|e|i)n允许t与n中的三个字符可以任选一个, 匹配的字符串例如tan, tin, 和Pakistan, 但不包括teen, mountain或者tune. 作为可选择的,表达式t(a|e|i)n 也可以被理解为字符列taein. 表4列出了这些字符元。REGEXP_LIKE 操作下面的SQL查询中Where从句显示了怎么使用REGEXP_LIKE函数， 在ZIP列中查询满足正则表达式的模式。 查询返回ZIPCODE表中包含字符不全是数字的行:Select zipFROM zipcodeWhere REGEXP_LIKE(zip, )ZIP-ab123123xy007ababcxy这个例子中正则表达式只是由字符元组成, 更多明确的数字字符类用冒号和方括号标示. 第二组括号嵌套了一个字符类列表，就像前面说的，这是个必不可少的， 你可以用POSIX字符类来创建字符列表REGEXP_INSTR函数这个函数返回模式的开始位置， 有点像INSTR函数, 语法见表6，两个函数不同之处是REGECP_INSTR可以让你指定一个模式而不是特定的查找字符串，这样就提供了强大的功能, 下面的例子使用REGEXP_INSTR返回字符串Joe Smith, 10045 Berry Lane, San Joseph, CA 91234中5个邮政编码的开始位置，如果正则表达式写成5, 得到的将是门牌号而不是邮编, 因为10045是第一个以5个数字出现的模式， 因此必须用字符元$表明是最后一个,这个函数将返回邮编的开始位置而不管门牌号：Select REGEXP_INSTR(Joe Smith, 10045 Berry Lane, San Joseph, CA 91234,5$)AS rx_instrFROM dualRX_INSTR-45写一些复杂的模式我们扩展一下前面邮政编码的模式匹配， 包括一个可选的4个数字，正则表达式为:5(-4)?$. 如果你的源字符串以5个数字的邮编代码或者5个数字的邮编 4个数字的邮编组成, 这个正则表达式可以用来找到开始位置Select REGEXP_INSTR(Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234, 5(-4)?$)AS starts_atFROM dualSTARTS_AT-44这个例子中括号内的表达式(-4) 被重复0次或者1次， 由?循环操作控制。如果试图用传统的SQL函数取得这样的结果对SQL专家来说都是很困难的。 要想对正则表达式的各种变量有个了解， 参见表7.REGEXP_SUBSTR 函数和SUBSTR函数差不多, 分解出字符串的一部分, 表8列出了这个函数的用法。 下面的例子中，返回匹配模式,*,的字符串，正则表达式查找逗号后面跟一个空格，后面跟着非,的字符或者字符串, 最后以逗号结束。Select REGEXP_SUBSTR(first field, second field , third field, ,*,)FROM dualREGEXP_SUBSTR(FIR-, second field ,REGEXP_REPLACE 函数传统的REPLACE函数, 用一个字符串代替另一个， 假设你的数据里面有很多的空白字符要用一个空白字符替换， 用REPLACE函数的话， 必须指出替换多少个空白字符。下面的例子中在Joe和Smith中间有3个空白,REPLACE函数的参数指定用一个空白字符替换两个空白字符, 这样就把原来的一个没有替换Select REPLACE(Joe Smith, )AS replaceFROM dualREPLACE-Joe SmithREGEXP_RELPLACE 函数会好一些， 语法在表9中列出。 下面的例子用一个空格替换两个或者多个空格。 ()子表达式包含一个空格， 可以重复一次或者多次， 由2,指示.Select REGEXP_REPLACE(Joe Smith,( )2, )AS RX_REPLACEFROM dualRX_REPLACE-Joe SmithBackreference正则表达式的一个很有用的特点是可以保存子表达式以后使用， 被称为Backreferencing(在表10中汇总). 允许复杂的替换能力如调整一个模式到新的位置或者指示被代替的字符或者单词的位置. 被匹配的子表达式存储在临时缓冲区中， 缓冲区从左到右编号, 通过数字符号访问。下面的例子列出了把名字 ELLen Hildi Smith 变成Smith, Ellen Hildi.Select REGEXP_REPLACE(Ellen Hildi Smith,(.*) (.*) (.*), 3, 1 2)FROM dualREGEXP_REPLACE(EL-Smith, Ellen Hildi这个SQL语句显示了3个独立的以圆括号表示的子表达式, 每个子表达式匹配任意一个字符元, 圆括号创建的子表达式获得的值以数字参考， 第一个子表达式被赋予1, 第二个被赋予2.等等. Backreference在最后一个参数中使用， 有效的返回了替换字符串的格式(包括逗号和空格). 表11列出了这种正则表达式的各个组成部分Backreference 替换， 格式化， 取值方面很有用的. 下面的例子用REGEXP_SUBSTR函数来查找重复出现的以空格分开的词。 显示的结果指出重复的子串。Select REGEXP_SUBSTR(The final test is is the implementation,( )( )1) AS substrFROM dualSUBSTR-is is匹配参数选项你可能已经注意到了正则表达式操作和函数包含了一个可选的匹配参数. 这个参数可以控制大小写, 匹配新一行的字符， 合并多行输入。正则表达式的实践应用正则表达式不仅可以用在查询中， 还可以用在任何可以使用SQL操作或者函数的地方，例如PL/SQL语言中. 可以在触发器中利用正则表达式的功能来检查数据有效性， 产生， 或者提取数值。下面的例子演示了怎么用REGEXP_LIKE