create or replace function isnumeric (str in varchar2)
return number
is
v_str float;
begin
if str is null
then
return 0;
else
begin
select to_number (str)
into v_str
from dual;
exception
when invalid_number
then
return 0;
end;
return 1;
end if;
end isnumeric;
2. 利用 regexp_like
create or replace function isnumeric (str in varchar2)
return number
is
begin
if str is null
then
return 0;
else
if regexp_like (str, '^(-{0,1}+{0,1})[0-9]+(.{0,1}[0-9]+)$')
then
return 1;
else
return 0;
end if;
end if;
end isnumeric;
3. 利用 translate
create or replace function isnumeric (str in varchar2)
return number
is
v_str varchar2 (1000);
begin
if str is null
then
return 0;
else
v_str := translate (str, '.0123456789', '.');
if v_str = '.' or v_str = '+.' or v_str = '-.' or v_str is null
then
return 1;
else
return 0;
end if;
end if;
end isnumeric;
