没有现成的函数--第一种方法 通过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 numberSQL> select decode(replace(translate('123.456','0123456789.',' '),' ',''),null,
2 'is number','is not a number') from dual;DECODE(RE
---------
is numberSQL>
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 numberSQL> select decode(replace(translate('123.456','0123456789.',' '),' ',''),null,
2 'is number','is not a number') from dual;DECODE(RE
---------
is numberSQL>
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货