oracle正则表达式函数简单总结(一)

问题：Oracle 搜索短信内容的时候能用正则表达式不？把短信内容中含有数字的（长度不限）筛选出来？

答案是显然的，oracle 10G的正则表达式极大滴提高了SQL灵活性，至少是2个月之前看过，囿于本棱的懒惰，
一直没记，sigh。本着“吃不下那么多猪肉也要多看看猪跑”的原则，粗略一记，很多时候来不及一一细陈细品，因为等你罗里八嗦品完陈完，黄花菜都凉了。

 

overview:主要是4个正则表达式函数和正则表达式中的元字符。REGEXP_LIKE、REGEXP_REPLACE、REGEXP_INSTR、REGEXP_SUBSTR，
愚以为要想写出灵活简洁的SQL好好的利用正则表达式，必须很熟练元字符的含义，偶不熟,sigh~

 

①REGEXP_LIKE(string x,pattern[,match_option])
   其中x是源字符串、pattern就是正则表达式、match_option默认匹配项，该参数可被设置为：
   c:说明在进行匹配时区分大小写(默认选项)
   i:说明在进行匹配时不区分大小写
   n:允许使用可以匹配任意字符的操作符
   m:将x作为一个包含多行的字符串
   偶觉得吧，里面难的主要是pattern。
     
eg1:表test_case
ID1
0
4
0
0
5
6
11
9
1936
1967
1968
1969

select tc.id1
from test_case tc
where regexp_like(tc.id1,'^19[0-9][6-9]$')
-----------------------------------------
1936 
1967
1968
1969

eg2:含有数字的短信内容：
select *
from msg_info partition(PART21) m
where regexp_like(m.CONTENT,'[0-9]')

eg3:
abcdefg
ABCDEFG

select *
from test_abc ta
where regexp_like(ta.log_string,'^a','i')
------
abcdefg
ABCDEFG

select *
from test_abc ta
where regexp_like(ta.log_string,'^a','c')
------
abcdefg

 

 

 

 

②REGEXP_INSTR(x,pattern[,start,occurrence,return_option,match_option]]]])
  其中x为源字符串，在x中查找pattern,并返回pattern所在的位置。后面的四个为可选参数
  start 说明开始查找的位置
  occurrence 说明应该返回第几次出现pattern的位置
  return_option 说明应该返回什么证书。若该参数为0，则说明要返回的正数是x中的一个
  字符的位置；若该参数为非0的整数，则说明要返回的整数为x中出现pattern之后的字符的
  位置。
  match_option修改默认的匹配设置：c  i  n m.
select REGEXP_INSTR('Never mix cards and whisky unless you were weaned on Irish poteen,” Gerald told Pork gravely the same evening, as Pork assisted him to bed. And the valet, who had begun to attempt a brogue out of admiration for his new master, made requisite answer in a combination of Geechee and County Meath that would have puzzled anyone except those two alone.','m[[:alpha:]]{2}')
from dual

----

7

--------会找到目前mix所在的位置

 

select REGEXP_INSTR('Never mix cards and whisky unless you were weaned on Irish poteen,” Gerald told Pork gravely the same evening, as Pork assisted him to bed. And the valet, who had begun to attempt a brogue out of admiration for his new master, made requisite answer in a combination of Geechee and County Meath that would have puzzled anyone except those two alone.','m[[:alpha:]]{3}'，1)
from dual

-----

199

--------会找到目前Meath开始的位置。

 

③REGEXP_replace(x,pattern[,replace_string,start,occurrence,return_option,match_option]]]]])
动作为从x查找pattern,并用replace_string替换掉，其他同regexp_instr()函数的参数完全相同。

 

select REGEXP_REPLACE('The muddy Flint River, running silently between walls of pine and water oak covered with tangled vines ','m[[:alpha:]]{4}','MANGO',1,1,'i')
from dual

-------输出为

The MANGO Flint River, running silently between walls of pine and water oak covered with tangled vines

 

 

④regexp_substr(x,pattern[,start,occurrence,match_option]]]]])
返回源字符串中可以匹配pattern的一个子字符串，其开始位置由start指定。其他选项的意思与REGEXP_INSTR()
其他参数相同。

select REGEXP_SUBSTR('The muddy Flint River, running silently between walls of pine and water oak covered with tangled vines ','m[[:alpha:]]{4}',1,1,'i')
from dual

------输出为

muddy 

 

参考：
Oracle Database SQL Reference
Oracle Database Globalization Support Guide
Application Developer's Guide
-----这些online文档里都能找到。

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/toc.htm
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm#sthref534
http://www.oracle.com/technology/global/cn/obe/obe10gdb/develop/regexp/regexp.htm
http://www.oracle.com/technology/global/cn/oramag/webcolumns/2003/techarticles/rischert_regexp_pt2.html

 

 

后续问题：

元字符、绑定变量、PL/SQL中的变量呢 规则是不是the same apply呢？

 

转载于:https://www.cnblogs.com/gracejiang/archive/2011/01/10/5890435.html