Reg Exp in Oracle 10g

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

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

One thought on “Reg Exp in Oracle 10g

Comments are closed.

Next Post

KC Session on ANT

On Tuesday we had a very good session with the Knowledge Center for Web & Java on ANT, the Jakarta “make”-tool. We started with the usual roundup of everyones current activities and recent findings. We discussed the highlight in the area of Java/J2EE of the ODTUG 2004 and JavaOne 2004 […]