Reg Exp in Oracle 10g

1
Share this on .. Tweet about this on Twitter0Share on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

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 blade voor achter bla bla
achter*|*veurveur@achter

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

Share this on .. Tweet about this on Twitter0Share on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

About Author

Consultant at AMIS