sqloracle-database

Oracle retrieve only number in string

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)

Solution

  • 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