24

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?

Improve this question
9
  • 1
    You can use inbuilt function regex_replace, SELECT REGEXP_REPLACE(column_name,'[[:alpha:]]') from tbl, at least you should search in Google, see this(community.oracle.com/thread/598281) oracle community provides some usefull ways Commented Sep 30, 2015 at 8:26
  • I could use a good example. I'm not good with Oracle Commented Sep 30, 2015 at 8:27
  • 2
    "I need to extract all the numbers from this string" - what should happen if the input string contains several numbers? E.g. what should your function return for the input '1234abc5678' ? Commented Sep 30, 2015 at 8:46
  • @FrankSchmitt, given the format of the values stored on that column, there is no need to consider this possibility. Commented Sep 30, 2015 at 8:52
  • Your example query seems to have errors, i.e. single quotes around table_name (and possibly also incorrectly around column_name). Also why would you want to have the extracted number also in the column list, when you already know exactly what it is, as you have specified it in the WHERE condition? Commented Dec 29, 2018 at 0:01

6 Answers 6

Reset to default
70

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I don't need to modify the sql query, I need that function that I can call
REGEXP_REPLACE is the function you would call to extract the digits.
However, I've added the function you can write to call regexp_replace for you.
Yup, Overkill is my other name. Thank you.
@ABHISHEK D: Are you looking for select regexp_replace(column_name, '[^0-9]', '') as my_numeric_value from mytable;?
|
9

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.

Improve this answer

2 Comments

This still returns text (character type), not a number. The answer would also benefit from placing it in the example query which was shown in the question.
how to get 569 i.e., numbers from the last character?
1

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

Improve this answer

1 Comment

easier way to extract the first set of numbers in a string can be select regexp_substr(h.HIST_OBSE,'[0-9]+','')
0

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:]]*')
Improve this answer

1 Comment

This has a typo (the opening single quote around the text doubled) and still returns text (character type), not a number. The answer would also benefit from placing it in the example query which was shown in the question.
0

To extract charecters from a string

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

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
-1

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;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.