Oracle 10g正则表达式

最新推荐文章于 2024-10-18 17:33:55 发布

levin_li

最新推荐文章于 2024-10-18 17:33:55 发布

阅读量217

点赞数

分类专栏： Database 文章标签： oracle insert 正则表达式 string function sql

Database 专栏收录该内容

65 篇文章 0 订阅

订阅专栏

ORACLE终于在10G中提供了对正则表达式的支持，以前那些需要通过LIKE来进行的复杂的匹配就可以通过使用正则表达式更简单的实现。
ORACLE中的支持正则表达式的函数主要有下面四个：
 

 1，REGEXP_LIKE ：与LIKE的功能相似
2，REGEXP_INSTR ：与INSTR的功能相似
3，REGEXP_SUBSTR ：与SUBSTR的功能相似
4，REGEXP_REPLACE ：与REPLACE的功能相似
在新的函数中使用正则表达式来代替通配符‘%’和‘_’。
正则表达式由标准的元字符（metacharacters）所构成：
'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。
'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹配 'n' 或 'r'。
'.' 匹配除换行符 n之外的任何单字符。
'?' 匹配前面的子表达式零次或一次。
'+' 匹配前面的子表达式一次或多次。
'*' 匹配前面的子表达式零次或多次。
'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。
'( )' 标记一个子表达式的开始和结束位置。
'[]' 标记一个中括号表达式。
'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。
num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。
字符簇：
[[:alpha:]] 任何字母。
[[:digit:]] 任何数字。
[[:alnum:]] 任何字母和数字。
[[:space:]] 任何白字符。
[[:upper:]] 任何大写字母。
[[:lower:]] 任何小写字母。
[[:punct:]] 任何标点符号。
[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。
各种操作符的运算优先级
转义符
(), (?:), (?=), [] 圆括号和方括号
*, +, ?, {n}, {n,}, {n,m} 限定符
^, $, anymetacharacter 位置和顺序
| “或”操作
下面通过几个例子来具体说明这几个新函数的使用方法：
 
SQL> create table sunwg (id varchar2(100));
Table created.
SQL> insert into sunwg values ('<a href="http://sunwgneuqsoft.itpub.net/post/34741/447698">常见SQL访问索引的方式</a>');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from sunwg;
ID
----------------------------------------------------------------------------------------------------
<a href="http://sunwgneuqsoft.itpub.net/post/34741/447698">常见SQL访问索引的方式</a>
1， REGEXP_LIKE
REGEXP_LIKE与LIKE类似，用REGEXP_LIKE能实现的操作大部分都可以用LIKE实现，不过要简单方便得多。
<a>目标：查询表sunwg中是否存在类似与3XX41的记录？
LIKE：
select * from sunwg where id like '%3__41%';
REGEXP_LIKE
select * from sunwg where regexp_like(id,'3..41');
<b>目标：查询表sunwg中是否存在类似与3XX41的记录,并且XX必须是数字？
LIKE：
这个LIKE我就想出来很好的实现办法了，唯一想到就是截取出来后判断该字符串是不是纯数字的。
REGEXP_LIKE
select * from sunwg where regexp_like(id,'3[0-9]{2}41');
用REGEXP_LIKE则可以简单快捷的得到结果。其他几个函数也都有类似的情况，下面的函数就不具体比较差异了，仅仅给出常用的用法。
2， REGEXP_INSTR
<a>目标：查询表sunwg中是否存在类似与3XX41的字符串第一次出现的位置？
SQL> select regexp_instr(id,'3..41',1,1) from sunwg;
REGEXP_INSTR(ID,'3..41',1,1)
----------------------------
46
SQL> select substr(id,46,5) from sunwg;
SUBST
-----
34741
3， REGEXP_SUBSTR
<a>目标：截取出表sunwg中的URL地址？
SQL> select regexp_substr(id,'http[0-9a-zA-Z/:.]+') from sunwg;
REGEXP_SUBSTR(ID,'HTTP[0-9A-ZA-Z/:.]+')
----------------------------------------------------------------------------------------------------
http://sunwgneuqsoft.itpub.net/post/34741/447698
4， REGEXP_REPLACE
<a>目标：替换表sunwg中的URL的地址为www.163.com?
SQL> select regexp_replace(id,'http[0-9a-zA-Z/:.]+','www.163.com') from sunwg;
REGEXP_REPLACE(ID,'HTTP[0-9A-ZA-Z/:.]+','WWW.163.COM')
------------------------------------------------------------------------------------------------------------------------------------------------------
<a href="www.163.com">常见SQL访问索引的方式</a>
 
从上面的例子可以看得出来这几个支持正则表达式的函数是十分强大的，合理的加以使用一定会使你写出的SQL更加简单高效。 
 
    regexp_substr

regexp_substr (string, pattern, position) regexp_substr (string, pattern, position, occurence) regexp_substr (string, pattern, position, occurence, parameters)


parameters can be a combination of
regexp_substr (string, pattern) 
   
 
    i: to match case insensitively
c: to match case sensitively
n: to make the dot (.) match new lines as well
m: to make ^ and $ match beginning and end of a line in a multiline string
 
   

     regexp_substr is an Oracle SQL function that enables  
    regular expressions in queries. It enhances the 'traditional'  
    substr. 
   
 Links
     See also  
    On splitting a string into words with regular expressions where a function uses  
    regexp_substr to split a string. 
   

     Then there is also  
    safe_to_number() where regexp_substr is used to convert strings to numbers. 
   
 
     regexp_instrregexp_instr (string, pattern) regexp_instr (string, pattern, position) regexp_instr (string, pattern, position, occurence) regexp_instr (string, pattern, position, occurence, return-option)regexp_instr (string, pattern, position, occurence, return-option, parameters) 
    
 Parameters 
     parameters can be a combination of 
     i: to match case insensitively
c: to match case sensitively
n: to make the dot (.) match new lines as well
m: to make ^ and $ match beginning and end of a line in a multiline string
x: to ignore white spaces.
 
    

      regexp_instr is an Oracle SQL function that enables  
     regular expressions in queries. It enhances the 'traditional'  
     instr. 
    
 
      regexp_likeregexp_like (string, pattern); regexp_like (string, pattern, parameters); 
     
 
      parameters can be a combination of 
      i: to match case insensitively
c: to match case sensitively
n: to make the dot (.) match new lines as well
m: to make ^ and $ match beginning and end of a line in a multiline string
 
     

       regexp_like is an Oracle SQL function that enables  
      regular expressions in queries. It enhances the «traditional»  
      like. 
     

       regexp_like is a  
      pattern condition. 
     
 Demonstration 
      create table strings ( str varchar2(30) ); create table patterns ( pat varchar2(50), dsc varchar2(30) ); 
     
 
      insert into patterns values ('^[[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4} 
     
 
      insert into strings values ('987-65-4321'); insert into strings values ('hello foo bar'); insert into strings values ('4987-65-4321'); insert into strings values ('hello FOO BAR'); insert into strings values ('-4.55'); insert into strings values ('987-65-43213'); insert into strings values ('4.55'); insert into strings values ('hello bar bar'); insert into strings values (' 4.55'); insert into strings values ('1234567890'); insert into strings values ('hello FOO FOO'); 
     
 
      select str,dsc from strings cross join patterns where regexp_like(str, pat) ; 
     
 
      STR DSC ------------------------------ ------------------------------ 987-65-4321 Social security number hello bar bar Repeated words hello FOO FOO Repeated words hello foo bar Only lowercase words hello bar bar Only lowercase words 1234567890 Only digits 987-65-4321 At least one digit 4987-65-4321 At least one digit -4.55 At least one digit 987-65-43213 At least one digit 4.55 At least one digit 4.55 At least one digit 1234567890 At least one digit -4.55 Number 4.55 Number 1234567890 Number 
     
 
      regexp_replaceregexp_replace (string, pattern) regexp_replace (string, pattern, replace-string)regexp_replace (string, pattern, replace-string, position) regexp_replace (string, pattern, replace-string, position, occurence) regexp_replace (string, pattern, replace-string, position, occurence, parameters) 
     
 
      parameters can be a combination of 
      i: to match case insensitively
c: to match case sensitively
n: to make the dot (.) match new lines as well
m: to make ^ and $ match beginning and end of a line in a multiline string
 
     

       regexp_substr is an Oracle SQL function that enables  
      regular expressions in queries. It enhances the 'traditional'  
      substr. 
     

       regexp_replace is an Oracle SQL function that enables  
      regular expressions in queries. It enhances the 'traditional'  
      replace. 
     
 Demonstration 
      create table strings ( str varchar2(30) ); create table patterns ( pat varchar2(60), repl varchar2(30), dsc varchar2(30) ); 
     
 
      insert into patterns values ('^[[:space:]]*[^[:space:]]+[[:space:]]+([^[:space:]]+).*', '/1', 'The 2nd word'); insert into patterns values ('^[^[:digit:]]*([[:digit:]]*/.?[[:digit:]]+).*' , '/1', 'The 1st number'); insert into patterns values ('^[^[:upper:]]*([[:upper:]]+).*' , '/1', 'Uppercase word'); 
     
 
      insert into strings values ('foo bar baz'); insert into strings values ('bla MOO 82.22 7.34 bla'); insert into strings values (' one two 3 four '); 
     
 
      column found format a20 select str, regexp_replace(str, pat, repl) found, dsc from strings cross join patterns where regexp_instr(str,pat) > 0; 
     
 
      STR FOUND DSC ------------------------------ -------------------- -------------------- foo bar baz bar The 2nd word bla MOO 82.22 7.34 bla MOO The 2nd word one two 3 four two The 2nd word bla MOO 82.22 7.34 bla 82.22 The 1st number one two 3 four 3 The 1st number bla MOO 82.22 7.34 bla MOO Uppercase word 
     
 Links
       See also  
      On using regexp_replace to format data. 
     
 , 'Social security number'); insert into patterns values ('[^[:alpha:]]([[:alpha:]]+)[^[:alpha:]] */1' , 'Repeated words'); insert into patterns values ('^([[:lower:]]| )* 
  
 
   ___FCKpd___5 
  
 
   ___FCKpd___6 
  
 
   ___FCKpd___7 
  
 
   ___FCKpd___8 
  
 
   parameters can be a combination of 
   i: to match case insensitively
c: to match case sensitively
n: to make the dot (.) match new lines as well
m: to make ^ and $ match beginning and end of a line in a multiline string
 
  

    regexp_substr is an Oracle SQL function that enables  
   regular expressions in queries. It enhances the 'traditional'  
   substr. 
  

    regexp_replace is an Oracle SQL function that enables  
   regular expressions in queries. It enhances the 'traditional'  
   replace. 
  
 Demonstration 
   ___FCKpd___9 
  
 
   ___FCKpd___10 
  
 
   ___FCKpd___11 
  
 
   ___FCKpd___12 
  
 
   ___FCKpd___13 
  
 Links
    See also  
   On using regexp_replace to format data. 
  

, 'Only lowercase words'); insert into patterns values ('^[[:digit:]]+

 
   ___FCKpd___8 
  
   parameters can be a combination of 
   i: to match case insensitively
c: to match case sensitively
n: to make the dot (.) match new lines as well
m: to make ^ and $ match beginning and end of a line in a multiline string
 
    regexp_substr is an Oracle SQL function that enables  
   regular expressions in queries. It enhances the 'traditional'  
   substr. 
  
    regexp_replace is an Oracle SQL function that enables  
   regular expressions in queries. It enhances the 'traditional'  
   replace. 
  
 Demonstration 
   ___FCKpd___9 
  
   ___FCKpd___10 
  
   ___FCKpd___11 
  
   ___FCKpd___12 
  
   ___FCKpd___13 
  
 Links
    See also  
   On using regexp_replace to format data.

, 'Only digits'); insert into patterns values ('[[:digit:]]' , 'At least one digit'); insert into patterns values ('^-?[[:digit:]]*/.?[[:digit:]]+

___FCKpd___5

___FCKpd___6

___FCKpd___7

 
  ___FCKpd___8 
 
  parameters can be a combination of 
  i: to match case insensitively
c: to match case sensitively
n: to make the dot (.) match new lines as well
m: to make ^ and $ match beginning and end of a line in a multiline string
 
   regexp_substr is an Oracle SQL function that enables  
  regular expressions in queries. It enhances the 'traditional'  
  substr. 
 
   regexp_replace is an Oracle SQL function that enables  
  regular expressions in queries. It enhances the 'traditional'  
  replace. 
 
 Demonstration 
  ___FCKpd___9 
 
  ___FCKpd___10 
 
  ___FCKpd___11 
 
  ___FCKpd___12 
 
  ___FCKpd___13 
 
 Links
   See also  
  On using regexp_replace to format data.

, 'Number');

___FCKpd___5

___FCKpd___6

___FCKpd___7

 
  ___FCKpd___8 
 
  parameters can be a combination of 
  i: to match case insensitively
c: to match case sensitively
n: to make the dot (.) match new lines as well
m: to make ^ and $ match beginning and end of a line in a multiline string
 
   regexp_substr is an Oracle SQL function that enables  
  regular expressions in queries. It enhances the 'traditional'  
  substr. 
 
   regexp_replace is an Oracle SQL function that enables  
  regular expressions in queries. It enhances the 'traditional'  
  replace. 
 
 Demonstration 
  ___FCKpd___9 
 
  ___FCKpd___10 
 
  ___FCKpd___11 
 
  ___FCKpd___12 
 
  ___FCKpd___13 
 
 Links
   See also  
  On using regexp_replace to format data.