Reg Exp in Oracle 10g

1

Last week I installed a 10g database on my laptop. I must say this was by far the easiest installation of an oracle database I ever did.
One of the coolest additions I came across so far is the use of Regular Expressions

Just some samples:

select every record with at least 4 r or R’s in the ‘naam’

select regexp_instr(naam,'r',1,4,0,'i') r4
, naam
from deelnemers_tmp
where regexp_instr(naam,'r',1,4,0,'i') >= 1 ;

replace every fourth R with an ‘*’

select regexp_replace(naam,'r','*' ,1,4,'i')
, naam
from deelnemers_tmp
where regexp_instr(naam,'r',1,4,0,'i') >= 1 ;

you can even use back references!

select regexp_replace(text,'(v..r).(achter)','2*|*1' ,1,1,'i')
, text
from regexp_test;

Replaced Original
de achter*|*voor bla bla de voor achter bla bla
achter*|*veur veur@achter

I’m not gonna explain them. Check out the oracle docs and be amazed.

Share.

About Author