Geek Answers Handbook

SQL How to extract numbers from a string?

I am working on a query in SQL that should be able to retrieve numbers with different / random durations from the beginning of a text string.

Text line: devil number 666 is not 8888.
Text line: 12345 devil number is my PIN, that is 6666.

I want to hit the column

666
12345
+4
source share
2 answers

Use a combination of Substrandinstr

SELECT Substr (textstring, 1,instr(textstring,' ') - 1) AS Output
FROM yourtable

Result:

OUTPUT
666
12345

Use this if you have text at the beginning, for example. aa12345 devils number is my PIN, that is 6666.because it uses a function REGEXP_REPLACE.

SELECT REGEXP_REPLACE(Substr (textstring, 1,instr(textstring,' ') - 1), '[[:alpha:]]','') AS Output
FROM yourtable

SQL Fiddle: http://sqlfiddle.com/#!4/8edc9/1/0

+3
source

, , , instr/substr:

SQL> with tbl(data) as (
     select '666 devils number is not 8888' from dual
     union
     select '12345 devils number is my PIN, that is 6666' from dual
     union
     select 'aa12345 devils number is my PIN, that is 6666' from dual
   )
   select regexp_substr(data, '^\D*(\d+) ', 1, 1, null, 1) first_nbr
   from tbl;

FIRST_NBR
---------------------------------------------
12345
666
12345

SQL>
0

More articles:


All Articles