oracle 正则表达式

with temp as(
select 'BR.777-1' A from dual
union all
select 'BS.6/175' A from dual
union all
select 'D.140 Supplement 1' A from dual
union all
select 'E.191(2000) Amendment 1' A from dual
)
select substr(A,0,instr(A,'.')-1),
substr(A,instr(A,'.') + 1,regexp_instr(A,'[^.[:alnum:]]') - (instr(A,'.') + 1)),--除点符号，数字和字符
substr(substr(A,instr(A,'.') + 1,length(A) - instr(A,'.')),regexp_instr(substr(A,instr(A,'.') +1,length(A) - instr(A,'.')),'[^[:alnum:]]'))
 from temp

 

 

2.

with A as(
select 110 yid,'殷剑锋' br,to_date('2010-07-11','yyyy-mm-dd') rdate from dual
),B as(
select 110 yid,'距离入院7天' d1,'距离打针-2天' d2,'距离打针二7天' d3,'距离打针三10天' d4 from dual
)
select br,rdate,rdate + to_number(REGEXP_REPLACE(d1,'[^[:digit:]]',' ')) 第一次打针时间, --把d1为非数字替换为空
rdate + to_number(REGEXP_REPLACE(d1,'[^[:digit:]]',' '))
+ to_number(REGEXP_REPLACE(d2,'[^[:digit:]]',' ')) 第二次打针时间
 from(
select BR,Rdate,D1,D2,D3,D4 from A,B where A.yid = B.yid
)

 

WITH table1 AS(
  SELECT '001' ID,'abc def' CONTENT FROM dual
  UNION ALL SELECT '002','abc-def hij abc' FROM dual
  UNION ALL SELECT '003','hij def' FROM dual
  UNION ALL SELECT '004','bbb' FROM dual
  )
select id,  REGEXP_REPLACE(CONTENT,'[[:punct:]]',' ') d1 from table1 --把符号替换为空格

3.查询前三个字符为大写字符+‘_’ + 三个数字

with temp as(
select 'ABC_123' a from dual
union all
select 'ABb_123' a from dual
union all
select 'ADC_12' a from dual
union all
select 'BC_123' a from dual
)
select * from temp where regexp_like(a,'[[:upper:]]{3,3}_[[:digit:]]{3,3}')

 

4.电话号码的正则表达式

select * from temp where length(telno)=11 and regexp_like(telno,'^13[[:digit:]]|^15[0|3|6|7|8|9][[:digit:]]|^(18[8|9])[[:digit:]]')