整数:
select 1 from dual where ltrim(&a,'0123456789') is null;
浮点数:
select 1 from dual where ltrim(replace(&a,'.'),'0123456789') is null and length(&a)-length(replace(&a,'.'))=1;
负整数:
select 1 from dual where ltrim(replace(&a,'-'),'0123456789') is null and length(&a)-length(replace(&a,'-'))=1 and substr(&a,1,1)='-' ;
select 1 from dual where rtrim(&a),'0123456789')='-';
负浮点数:
select 1 from dual where ltrim(replace(replace(&a,'.'),'-'),'0123456789') is null and length(&a)-length(replace(&a,'.'))=1 and length(&a)-length(replace(&a,'-'))=1 and substr(&a,1,1)='-';
select 1 from dual where rtrim(replace(&a,'.')),'0123456789')='-' and length(&a)-length(replace(&a,'.'))=1 and substr(&a,1,1)='-';--substr(&a,1,1)='-',防止小数点"."出现在第一位
或者,创建函数进行判断
hyacinth在oldwain的基础上进行的修改
代码:
scott@ORCL>createorreplacefunctionisnumber(c varchar2)2returnnumber
3 is
4 n number;5 begin
6 begin
7 n:=to_number(c);
~~~~~~~~~~~~~~~~~~改成n:=to_number(nvl(c,'a')),否则空值会认为是数字
8 exception when others then
9return0;10 end;11return1;12 end;13/函数已创建。
scott@ORCL>select isnumber('234')from dual;ISNUMBER('234')
---------------@ORCL>select isnumber('234b')from dual;ISNUMBER('234B')