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?

Possible duplicate of [Query to get only numbers from a string](http://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string) — Andrii Litvinov, Apr 22 '17 at 15:15
To be sure: do you need the number '1' as a string or as an integer / decimal? — Guido Leenders, Apr 23 '17 at 08:19

Patrick Hofman · Accepted Answer · 2017-04-23T08:23:14.390

2

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

edited Apr 23 '17 at 08:23

answered Apr 22 '17 at 17:40

Patrick Hofman

153,850
22
249
325

How to extract number from a string

1 Answers1