Oracle中如何判断是否为数字？

[chncaesar]

如题，现有Oracle数据库的表A，字段B，类型为Varchar2(32)。
用SQL文（可以为多条，最好一条）判断该字段的数据是字符串还是数字（0-9）。
紧急求救，给了答案马上给分，谢谢！

[沝林]

SQL> select nvl2(translate('1224312321','\1234567890','\'),'is characters','is number') from dual;

NVL2(TRANSLATE('1224312321','\
------------------------------
is number

SQL> select nvl2(translate('122431a2321','\1234567890','\'),'is characters,'is number') from dual;

NVL2(TRANSLATE('122431A2321','
------------------------------
is not number

[沝林]

SQL> select nvl2(translate('1224312321','\1234567890','\'),'is not number','is number') from dual;

NVL2(TRANSLATE('1224312321','\
------------------------------
is number

SQL> select nvl2(translate('122431a2321','\1234567890','\'),'is not number','is number') from dual;

NVL2(TRANSLATE('122431A2321','
------------------------------
is not number

[沝林]

select nvl2(translate(a.b,'\1234567890','\'),'is characters','is number') from a;

[chncaesar]

请各位牛人解释下代码，谢谢。

[bluecocoqd]

偷学：
为数字的
select trim(translate(A.B,'1234567890',' '))c from A where A.c is null

[yw19810304]

select nvl2(translate(字段,'\1234567890','\'),'is characters','is number') from 表

[bzszp]

测试数据库版本：
8.1.6 测试操作系统：windows2000 server
--第一种方法 通过to_number转换是否发生异常来判断
SQL> create or replace function f_str_or_num(str varchar2) return varchar2 is
2 v_num number;
3 v_return varchar2(60);
4 begin
5 v_num:=to_number(str);
6 v_return:=str||' is a number string!';
7 return v_return;
8 exception when others then
9 v_return:=str||' is not a number string!';
10 return v_return;
11 end f_str_or_num;
12 /
Function created.
SQL> select f_str_or_num('123.56') from dual;
F_STR_OR_NUM('123.56')
--------------------------------------------------------------------------------
123.56 is a number string!
SQL> select f_str_or_num('12aa.56') from dual;
F_STR_OR_NUM('12AA.56')
--------------------------------------------------------------------------------
12aa.56 is not a number string!
SQL>
--第二种通过translate函数来以及其他相关函数来实现
SQL> select decode(replace(translate('12a3.456','0123456789.',' '),' ',''),null, 2 'is number','is not a number') from dual;

DECODE(REPLACE( ---------------
is not a number

SQL> select decode(replace(translate('123.456','0123456789.',' '),' ',''),null, 2 'is number','is not a number') from dual;

DECODE(RE
---------
is number

SQL>