Avatar of toooki
toooki
 asked on

Oracle Database string search question

I have a CLOB field in the Oracle database. The field content is like this:

This is the log of the run procedure1 .... Tes1 took .. Test 2 took ...Time for Stage1: 164s(2m). ...... This is the log of the run procedure1 .... Tes1 took .. Test 2 took ...Time for Stage2: 144s(2m). ... This is the log of the run procedure1 .... Tes1 took .. Test 2 took ...Time for Stage3: 114s(2m).


How could I extract the values so that the output is like:
164s(2m)

And the next one;
144s(2m).

And the next one;
114s(2m).

I tried like this :


select  REGEXP_SUBSTR(myclobfield , '[^Time for Stage1: ]+', 1, 1) as stage1_time
 from mytab where id = 1;

select  REGEXP_SUBSTR(myclobfield , '[^Time for Stage2: ]+', 1, 1) as stage1_time
 from mytab where id = 1;

select  REGEXP_SUBSTR(myclobfield , '[^Time for Stage3: ]+', 1, 1) as stage1_time
 from mytab where id = 1;

But I am not coming close. Any comment will help...thank you.
Oracle DatabaseDatabases

Avatar of undefined
Last Comment
toooki

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Sean Stuber

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sean Stuber

sub expression support on regexp_substr is only for 11gR2

if you have an earlier version you'll need to nest the calls within another regexp

REGEXP_SUBSTR(REGEXP_SUBSTR(myclobfield, 'Time for Stage1: [^.]+'), '[^ ]+$') AS stage1_time,
REGEXP_SUBSTR(REGEXP_SUBSTR(myclobfield, 'Time for Stage2: [^.]+'), '[^ ]+$') AS stage2_time,
REGEXP_SUBSTR(REGEXP_SUBSTR(myclobfield, 'Time for Stage3: [^.]+'), '[^ ]+$') AS stage3_time
SOLUTION
paquicuba

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
paquicuba

Sean Stuber

thanks for the correction, they are not supported in 10g
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
paquicuba

No problem buddy!!
toooki

ASKER
Thank you !!

Mine is Oracle 11gR2.

So the query worked perfectly.
Thanks a lot.