CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle-sqldevelopersqltools
Vali S
Vali S

Reputation: 1461

Extract number from string with Oracle function

I need to create an Oracle DB function that takes a string as parameter. The string contains letters and numbers. I need to extract all the numbers from this string. For example, if I have a string like RO1234, I need to be able to use a function, say extract_number('RO1234'), and the result would be 1234.

To be even more precise, this is the kind of SQL query which this function would be used in.

SELECT DISTINCT column_name, extract_number(column_name) 
FROM table_name
WHERE extract_number(column_name) = 1234;

QUESTION: How do I add a function like that to my Oracle database, in order to be able to use it like in the example above, using any of Oracle SQL Developer or SQLTools client applications?

Upvotes: 24

Views: 188632

Answers (6)

Assaduzzaman Sarker
Assaduzzaman Sarker

Reputation: 1

To extract charecters from a string

SELECT REGEXP_REPLACE(column_name,'[^[:alpha:]]') alpha FROM DUAL

Upvotes: 0

nsola
nsola

Reputation: 985

In order to extract month and a year from a string 'A0807' I did the following in PL/SQL:

DECLARE
    lv_promo_code VARCHAR2(10) := 'A0807X';
    lv_promo_num VARCHAR2(5);
    lv_promo_month NUMBER(4);
    lv_promo_year NUMBER(4);
    BEGIN
    lv_promo_num := REGEXP_SUBSTR(lv_promo_code, '(\d)(\d)(\d)(\d)');

lv_promo_month := EXTRACT(month from to_date(lv_promo_num, 'MMYY'));
DBMS_OUTPUT.PUT_LINE(lv_promo_month);
lv_promo_year := EXTRACT(year from to_date(lv_promo_num, 'MMYY'));
DBMS_OUTPUT.PUT_LINE(lv_promo_year);
END;

Upvotes: -1

Alejandro Ovalle
Alejandro Ovalle

Reputation: 9

This works for me, I only need first numbers in string:

TO_NUMBER(regexp_substr(h.HIST_OBSE, '\.*[[:digit:]]+\.*[[:digit:]]*'))

the field had the following string: "(43 Paginas) REGLAS DE PARTICIPACION".

result field: 43

Upvotes: 1

Parasram Pawar
Parasram Pawar

Reputation: 93

You can use regular expressions for extracting the number from string. Lets check it. Suppose this is the string mixing text and numbers 'stack12345overflow569'. This one should work:

select regexp_replace('stack12345overflow569', '[[:alpha:]]|_') as numbers from dual;

which will return "12345569".

also you can use this one:

select regexp_replace('stack12345overflow569', '[^0-9]', '') as numbers,
       regexp_replace('Stack12345OverFlow569', '[^a-z and ^A-Z]', '') as characters
from dual

which will return "12345569" for numbers and "StackOverFlow" for characters.

Upvotes: 9

Iqbal Singh
Iqbal Singh

Reputation: 1

If you are looking for 1st Number with decimal as string has correct decimal places, you may try regexp_substr function like this:

regexp_substr('stack12.345overflow', '\.*[[:digit:]]+\.*[[:digit:]]*')

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You'd use REGEXP_REPLACE in order to remove all non-digit characters from a string:

select regexp_replace(column_name, '[^0-9]', '')
from mytable;

or

select regexp_replace(column_name, '[^[:digit:]]', '')
from mytable;

Of course you can write a function extract_number. It seems a bit like overkill though, to write a funtion that consists of only one function call itself.

create function extract_number(in_number varchar2) return varchar2 is
begin
  return regexp_replace(in_number, '[^[:digit:]]', '');
end;

Upvotes: 70

Related Questions