René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

use of SUBSTR and INSTR

The following example shows how to use SUBSTR and INSTR to extract strings of variable length. The table ALL_SOURCE is queried in order to find all system defined exceptions. 
select 
  substr
  (text,                                 -- text of which substr is to be taken
     instr(                              -- beginning of substring
        text,                            -- find 'EXCEPTION_INIT' within text and add length
        'EXCEPTION_INIT(')   
         +LENGTH('EXCEPTION_INIT('),
     instr(                              -- length of substr
       text,                             -- search text
         ',',                            -- find a ','
         instr(text, 'EXCEPTION_INIT(')  -- after the length calculated as
         +  LENGTH ('EXCEPTION_INIT('))  -- above
       -                                 -- and substract
         (instr(text, 'EXCEPTION_INIT(') -- the end position of
         +  LENGTH ('EXCEPTION_INIT('))  -- EXCEPTION_INIT
   ) "Exceptions"
from all_source where name = 'STANDARD' and type = 'PACKAGE' and text like '%EXCEPTION%'
/