sql - Extract number from string with Oracle function -

i need create oracle function takes string parameter. string contains letters , numbers. need extract numbers string. example, if have string ro1234, need able use function, extract_number(ro1234), , result 1234.

to more precise, kind of sql query function used in.

select distinct 'column_name', extract_number(column_name)  'table_name'  extract_number(column_name) = 1234; 

question: how add function oracle database, in order able use in example above, using of oracle sqldeveloper or sql tools client aplications?

you'd use regexp_replace in order remove non-digit characters string:

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

or

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

of course can write function extract_number. seems bit overkill though, write funtion consists of 1 function call itself.

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