René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

is_numeric [PL/SQL]

The following function can be used to verify that a string is numeric. It returns 1 if so, and 0 otherwise. 
create function is_numeric(str in varchar2) return number is
  v_number number(38);
begin
  v_number := to_number(str);
  return 1;

exception when value_error then

    return 0;

end is_numeric;
/

Demonstration

create table is_numeric_ex (
  col varchar2(10)
);

insert into is_numeric_ex values ('  100  ');
insert into is_numeric_ex values ('2e4');
insert into is_numeric_ex values ('-5.89');
insert into is_numeric_ex values ('+28.71');
insert into is_numeric_ex values ('bar');
insert into is_numeric_ex values ('999.99.99');
insert into is_numeric_ex values ('foo');

select * from is_numeric_ex
 where is_numeric(col) = 1;

COL
--------
  100
2e4
-5.89
+28.71