oracle如何判断字符串是数字类型,oracle中如何判断一个字符串是否数字

整数：

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')