Oracle Database string search question

toooki
toooki used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
REGEXP_SUBSTR(myclobfield,
                     'Time for Stage1: ([^.]+)',
                     1,
                     1,
                     NULL,
                     1
                    )
           AS stage1_time,
           REGEXP_SUBSTR(myclobfield,
                     'Time for Stage2: ([^.]+)',
                     1,
                     1,
                     NULL,
                     1
                    )
           AS stage2_time,
           REGEXP_SUBSTR(myclobfield,
                     'Time for Stage3: ([^.]+)',
                     1,
                     1,
                     NULL,
                     1
                    )
           AS stage3_time
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
I've been using subexpressions since 11gR1:

select * from v$version;

BANNER                                                                        
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production    
PL/SQL Release 11.1.0.7.0 - Production                                          
CORE      11.1.0.7.0      Production                                                        
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production                
NLSRTL Version 11.1.0.7.0 - Production                                          


SELECT REGEXP_SUBSTR('1234567890', '(123)(4(56)(78))', 1, 1, 'i', 2)
"REGEXP_SUBSTR" FROM DUAL;

REGEXP_SUBSTR
-------------
45678
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2011
Top Expert 2012

Commented:
thanks for the correction, they are not supported in 10g
No problem buddy!!

Author

Commented:
Thank you !!

Mine is Oracle 11gR2.

So the query worked perfectly.
Thanks a lot.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial