oracle正则的使用

csdn推荐

1、建表

create table person (first_name varchar2(20),
                     last_name varchar2(20),
                     email varchar2(40),
                     zip varchar2(20));
insert into PERSON (first_name, last_name, email, zip)
values ('Steven', 'Chen', 'steven@hp.com', '123456');
insert into PERSON (first_name, last_name, email, zip)
values ('James', 'Li', 'jamesli@sum.com' || chr(10) || 'lijames@oracle.com', '1b3d5f');
insert into PERSON (first_name, last_name, email, zip)
values ('Tina', 'Zhang', 'chillaxzx@163.com', '2456hd');
insert into PERSON (first_name, last_name, email, zip)
values ('Tom', 'Feng', 'Tomfeng@126.com', 'a654e5');
insert into PERSON (first_name, last_name, email, zip)
values ('Jonson', 'zhao', 'Jonson@google.com', 'edjksk');
insert into PERSON (first_name, last_name, email, zip)
values ('Vines', 'Wu', 'Vines@162.com', '2djks4');
commit;
select * from person;

2、通配符 ^ 行首，如果^在[]里面，表示非的意思，匹配字符串的开头位置，如果A是字符串的第一个字符，^A；非A开头的字符 ^[^A]$ 行尾，匹配字符串的末尾位置 如果B是字符串的最后一个字符，B$. 点匹配除null以外的任意单个字符* 匹配前面的字符0次或多次 ，ba*rk可以匹配 brk、bark、baark等等? 匹配前面的字符0次或1次 ， ba?rk可以匹配 bark、brk，但是不能匹配baark。+ 匹配前面的字符1次或多次 ，ba+rk可以匹配 bark、baark等等，但是不能匹配brk，也就是说，最少有以一次。{m} 匹配前面的字符恰好是m次， 其中n是整数 。hob{2}it可以匹配hobbit{m,} 匹配前面的字符至少是m次 。hob{2,}it可以匹配hobbit、hobbbit等等{m,n} 匹配前面的字符至少是m次，最多是n次 。hob{2,3}it可以匹配hobbit或者hobbbitx|y 匹配“或” x|y可以匹配x或者y(pattern) 括号中pattern是一个子正则表达式，匹配指定pattern模式的一个子表达式。 如：aaa(x|y)可以匹配aaax或者aaay。[abc] 可以匹配abc中的任何单个字符， hello[abc]可以匹配helloa,hellob,helloc[a-z] 可以匹配指定范围内的任何单个字符 。hell[a-z]可以匹配hello或者hellz[: :] 指定一个字符类，可以匹配该类中的任何字符

[[:alphanum:]] 可以匹配字符0-9、A-Z、a-z 任意数字和字母类似于[0-9a-zA-Z]

[[:alpha:]] 可以匹配字符A-Z、a-z 任意字母，类似于 [a-zA-Z]

[[:blank:]] 可以匹配空格或tab键

[[:digit:]] 可以匹配数字0-9 任意数字，类似于 [0-9]

[[:graph:]] 可以匹配非空字符

[[:lower:]] 可以匹配小写字母a-z

[[:print:]] 所有的可打印字符 与[[:graph:]]类似，不同之处在于[:print:]包括空格字符

[[:punct:]] 可以匹配标点符号.,""等等

[[:space:]] 可以匹配所有的空字符（不会打印出来）

[[:upper:]] 可以匹配大写字母A-Z

[[:xdigit:]] 可以匹配十六进制数字0-9、A-F、a-f

3、正则函数

匹配参数

（1）regexp_like 模糊匹配

regexp_like(列,匹配模式[,匹配参数])
--查询emp表中不是S开头或不是J开头的员工信息
select *
from emp
--where regexp_like(ename,'^[^SJ]');
where not regexp_like(ename,'^[SJ]');
--查询名字不以S或K结尾的员工信息(在中括号中表示取反有not in 的意思,不等于中括号里所有的)
select *
from emp
--where regexp_like(ename,'[^SK]$');
where not regexp_like(ename,'[SK]$');

（2）regexp_replace 用户替换字符串中某个值

regexp_replace(str1,匹配模式[,str3[,数1[,数2[,匹配参数]]]]) 
--匹配参数建立数一数二都有的情况下才能写
replace(str1,str2[,str3])  把str1中的str2整体替换成str3
str1:原字符串
str3:替换成的字符    不写表示替换为空
数1:从数1位开始(不写默认为1)    
数2:第数2次符合匹配模式的字符串,不写默认全替换(注:这里如果不写那也不能写匹配参数,不然系统会认为匹配参数就是他的数2)   
连起来是:把str1中从第数1位开始，第数2次符合匹配模式的字符串替换成str3
select 'helloworld', 
       regexp_replace('helloworld','l','*') a,
       regexp_replace('helloworld','l') b,
       regexp_replace('helloworld','ll','*') c,
       regexp_replace('helloworld','l','*',1,2) d,--从第一位找第二次l出现的位置
       regexp_replace('helloworld','l','*',4) e
from dual;
--把zip列所有的数字替换成*
select zip,
       regexp_replace(zip,'[0-9]','*')
from person;
--把zip列第一个数字替换成?
select zip,
       regexp_replace(zip,'[0-9]','?',1,1)
from person;
--查询zip列所有的字母部分  (常考)
select zip,
       regexp_replace(zip,'[^a-zA-Z]')       
from person;
--查询zip列所有的数字部分  (常考)
select zip,
       regexp_replace(zip,'[^0-9]')
from person;

（3）replace_instr 使用正则表达式返回搜索模式的起点和终点（整数）

3.regexp_instr(str,匹配模式[,数1[,数2[,数3[,匹配参数]]]]) 查找字符串位置  --返回数值
从第数1位开始找 找第数2次匹配的位置
instr（str1,str2[,数1[,数2]]）从数1开始，在str1查找str2字符第数2出现的位置
数1 --开始找的位置
数2 --匹配第几次出现
数3 --非0返回‘匹配的字符串’的最后一位位置的下一个位置，找不到返回0
数1 数2不写默认是1 
数3不写默认从前往后找
数3不写默认返回字符串中的第一个字符位置，
数3为非0返回字符串最末尾字符的后一位，找不到返回0
--查找zip列第一个非数字的字符的位置
select  zip,
        regexp_instr(zip,'[^0-9]') a,
        regexp_instr(zip,'[^0-9]',2,1,0) b,
        regexp_instr(zip,'[^0-9]',2,1,4) c,--第二位找第一次出现的后一位
        regexp_instr(zip,'[^0-9]',1,1) d,
        regexp_instr(zip,'[^0-9]',5,1) e,
        regexp_instr(zip,'[^0-9]',5,1,7) f  
from person;
--查找zip列第二次出现"字母和数字的组合"的位置(匹配字符串的第一个字符的位置)
select zip,
      regexp_instr(zip,'[a-zA-Z][0-9]',1,2)
from person;
--查找email列'@'第一次出现的位置, '.' 第一次出现的位置, 标点符号第一次出现的位置
select email,
       regexp_instr(email,'@'),
       regexp_instr(email,'.',1,1,0),
       regexp_instr(email,'[:.:]',1,1,0),
       regexp_instr(email,'[[:punct:]]')
from person;

（4）replace_substr 使用正则表达式返回指定返回串的起点和终点

regexp_substr(str,匹配模式[,数1[,数2[,匹配参数]]]) 截取字符串
substr(str,数1[,数2]) 从str中的第数1为截取数2位长度的字符    
           
数1 从第几位开始找 
数2 第几次匹配
从数1位开始找 截取第数2次匹配的字符串
数1 数2不写默认是1
--截取zip列第一个"数字+字母"的组合
select zip,
      regexp_substr(zip,'[0-9][a-z]',1,1,'i') a,
      regexp_substr(zip,'[0-9][a-zA-Z]')
from  person; 
--截取zip列第一个连续的字母串
select  zip,  
       regexp_substr(zip,'[a-zA-Z][a-zA-Z]+',1,1),
       regexp_substr(zip,'[a-zA-Z]{2,}',1,1),
       regexp_substr(zip,'[a-z]{2,}',1,1,'i')
from  person;
--截取'i love http://www.52oracle.com'第一次出现o开头e结尾中间任意四个字符的字符串
select 'i love http://www.52oracle.com',
       regexp_substr('i love http://www.52oracle.com','o....e')
from dual;

（5）replace_count 统计符合条件的字符出现的次数

regexp_count(source_char, pattern , position , match_param)
source:需要用来进行分析的字符串
pattern:字符（可以是正常字母数字等 a,1 等，也可以是正则表达式 /d /w  [a-z]等），整个函数的作用就是返回pattern在source中出现的次数值。
position:从字符串的那个位置开始分析，可以省略，默认值是1
match_param:匹配参数
匹配参数
‘i’ 用于不区分大小写的匹配
‘c’ 用于区分大小写的匹配
‘n’ 允许句点(.)作为通配符去匹配换行符。如果省略该参数，则句点将不匹配换行符
‘m’ 将源串视为多行。即Oracle 将^和$分别看作源串中任意位置任何行的开始和结束，而不是仅仅看作整个源串的开始或			  结束。如果省略该参数，则Oracle将源串看作一行。
‘x’ 忽略空格字符。默认情况下，空格字符与自身相匹配。
select regexp_count('hello-world','o') from dual

文章来源:https://blog.csdn.net/qq_43583887/article/details/139130669