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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
thanks for the correction, they are not supported in 10g
No problem buddy!!
ASKER
Thank you !!
Mine is Oracle 11gR2.
So the query worked perfectly.
Thanks a lot.
Mine is Oracle 11gR2.
So the query worked perfectly.
Thanks a lot.
if you have an earlier version you'll need to nest the calls within another regexp
REGEXP_SUBSTR(REGEXP_SUBST
REGEXP_SUBSTR(REGEXP_SUBST
REGEXP_SUBSTR(REGEXP_SUBST