Oracle retrieve only number in string

Question

In Oracle pl/sql, how do I retrieve only numbers from string.

e.g. 123abc -> 123 (remove alphabet)

e.g. 123*-*abc -> 123 (remove all special characters too)

Answer 1

Several options, but this should work:

select regexp_replace('123*-*abc', '[^[:digit:]]', '') from dual

This removes all non-digits from the input.

If using in pl/sql, you could do an assignment to a variable:

declare
    l_num number;
    l_string varchar2(20) := '123*-*abc';
begin
    l_num := regexp_replace(l_string, '[^[:digit:]]', '');
    dbms_output.put_line('Num is: ' || l_num);
end;

Output:

Num is: 123