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> create or replace function isnumber( c varchar2)
2 return number
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
9 return 0;
10 end;
11 return 1;
12 end;
13 /函数已创建。


scott@ORCL> select isnumber('234') from dual;
ISNUMBER('234')
---------------
1

scott@ORCL> select isnumber('234b') from dual;
ISNUMBER('234B')