Reg Exp in Oracle 10g

0 0
Read Time:50 Second

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.

About Post Author

Leon van Tegelen

Consultant at AMIS
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

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 […]
%d bloggers like this: