invantive-sqlinvantive-control

How to extract number from a string


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?


Solution

  • 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