CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
HeadOverFeet
HeadOverFeet

Reputation: 788

SQL How to extract numbers from a string?

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

Text string: 666 devils number is not 8888.
Text string: 12345 devils number is my PIN, that is 6666.

I want to get in a column

666
12345

Upvotes: 4

Views: 14001

Answers (2)

Gary_W
Gary_W

Reputation: 10360

This version utilizes a regular expression which gives you the first number whether or not it's preceded by text and does not use the ghastly nested instr/substr calls:

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>

Upvotes: 0

Matt
Matt

Reputation: 15061

Use a combination of Substr & instr

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

Result:

OUTPUT
666
12345

Use this if you have text at the beginning e.g. aa12345 devils number is my PIN, that is 6666. as it utilises the REGEXP_REPLACE function.

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

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

Upvotes: 3

Related Questions