Reg Exp in Oracle 10g

0 0

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;

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

About Post Author

Leon van Tegelen

Consultant at AMIS

leon.van.tegelen@amis.nl

Happy

0 0 %

Sad

0 0 %

Excited

0 0 %

Sleepy

0 0 %

Angry

0 0 %

Surprise

0 0 %

Related posts:

Putting Analytical Functions to good use – find tables with multiple foreign keys to the same referenced table Used port numbers by the Oracle OEM agent Using Oracle 11g Virtual Columns for implementation of Dynamic Business Rules Closing the Gapp on Oracle Open World? Mr. Thomas Kyte goes Dutch – Guru4Pro