CODEWITHSUNDEEP
invantive-sqlinvantive-control
H Jansen
H Jansen

Reputation: 319

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?

Upvotes: 2

Views: 70

Answers (1)

Patrick Hofman
Patrick Hofman

Reputation: 156938

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

Upvotes: 2

Related Questions