Link to home
Start Free TrialLog in
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.
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sean Stuber
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks for the correction, they are not supported in 10g
No problem buddy!!
Avatar of toooki

ASKER

Thank you !!

Mine is Oracle 11gR2.

So the query worked perfectly.
Thanks a lot.