How to extract number from a string

Question

I've a string like 'intercompany creditors {DEMO[[1]]}'. I want to extract only the numbers from the string, in example just '1'.

How to do this in Invantive SQL?

Answer 1

You should be able to do so with substr (get some piece of text from specific positions in the text) and instr (get the position from a specific piece of text inside some other text):

select substr
       ( d
       , instr(d, '[[') + 2
       , instr(d, ']]') - instr(d, '[[') - 2
       )
from   ( select 'intercompany creditors {DEMO[[1]]}' d 
         from dual@DataDictionary
       ) x