troubleshooting Question

Oracle Database string search question

Avatar of toooki
toooki asked on
DatabasesOracle Database
7 Comments2 Solutions820 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 2 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros