webcrazymaniac webcrazymaniac - 1 month ago 6
SQL Question

Extract number from string with Oracle function

I need to create an Oracle 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 SqlDeveloper or Sql Tools client aplications?

Answer

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;