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?

Hilarion
  • 820
  • 7
  • 21
Vali S
  • 1,471
  • 2
  • 10
  • 18
  • 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(https://community.oracle.com/thread/598281) oracle community provides some usefull ways – Vivek S. Sep 30 '15 at 08:26
  • I could use a good example. I'm not good with Oracle – Vali S Sep 30 '15 at 08: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' ? – Frank Schmitt Sep 30 '15 at 08:46
  • @FrankSchmitt, given the format of the values stored on that column, there is no need to consider this possibility. – Vali S Sep 30 '15 at 08: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? – Hilarion Dec 29 '18 at 00:01
  • @ValiS: The goal of StackOverflow is not just to provide answers to the ones asking questions, but also for those questions and answers serving others with similar problems. For that reason it's beneficial for others that the question is clear and complete. If there are errors, omissions or anything is unclear, the questions should be edited, regardless if everything was later explained in comments or answers. That's one of the core purposes of the "[edit](https://stackoverflow.com/posts/32861256/edit)" option being there. – Hilarion Dec 31 '18 at 09:20
  • @ValiS: I'm not sure where this is coming from. My intention was not to question your expertise, but give constructive feedback (suggest improvements). I don't see how my comments have indicated my lack of experience (although I might not have shown here anything to prove I have it). Even if I was unqualified with Oracle DB, as you suggest, this is not relevant to what is expected from StackOverflow users. See [here](https://stackoverflow.com/help/editing) on why and how editing is beneficial. – Hilarion Jan 02 '19 at 19:34
  • @ValiS: No, it's not OK to use single quotes around table/column names in Oracle. Single quoted string is a string literal (or - under some conditions - a date literal), not an Oracle identifier (as table/column names are). It's usually the same in most (if not all) other SQL dialects, not just Oracle's. Maybe you meant double quotes? Which may be OK (but may not be OK, as unquoted and quoted may result in a different identifier). You may also be confused by how you may be constructing your queries in some PHP tools/frameworks (i.e. a single-quoted PHP literal becomes an unquoted part of SQL). – Hilarion Jan 04 '19 at 21:54
  • You might want to take a look at this [Oracle community post on What is the difference between single quote (') and double quote(") ?](https://community.oracle.com/thread/978489) and this [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=3f83b4de07f267f32e0f77b8206ee3cc) on how using single/double/none quotes with Oracle DB makes a difference. – Hilarion Jan 04 '19 at 22:00

6 Answers6

69

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;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
10

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.

Hilarion
  • 820
  • 7
  • 21
Parasram Pawar
  • 103
  • 1
  • 6
  • 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. – Hilarion Dec 28 '18 at 23:59
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

Connor Low
  • 5,900
  • 3
  • 31
  • 52
Alejandro Ovalle
  • 9
  • 1
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:]]*')
Hilarion
  • 820
  • 7
  • 21
Iqbal Singh
  • 1
  • 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. – Hilarion Dec 28 '18 at 23:57
0

To extract charecters from a string

SELECT REGEXP_REPLACE(column_name,'[^[:alpha:]]') alpha FROM DUAL
Assaduzzaman Sarker
  • 1
  • 1
  • 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](/help/how-to-answer). – Community Mar 14 '22 at 11:18
-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;
nsola
  • 765
  • 6
  • 8