SQL> select nvl2(trim(translate('12333',' 123456789',' ')),'is not a number','is a number') from dual
2 /NVL2(TRIM(T
-----------
is a numberSQL> select nvl2(trim(translate('12w333',' 123456789',' ')),'is not a number','is a number') from dual
2 /NVL2(TRIM(TRANS
---------------
is not a number
解决方案 »
- 多个job一起执行后 其中一个无法得到正确结果
- power designer生成view的问题
- 提高dbms_job.submit的执行效率(跪求高手指教)
- 执行SQL报ORA-03113: end-of-file on communication channel错误,各位大侠,请帮忙看看!
- 触发器调用网络客户端程序时出错,why?
- 一sql查询的问题
- oracle - java
- 用了下good_hy写的ORACLE分页存储过程,总是返回不了结果集
- oracle中如何如何将变量包含在select 语句中?
- 求助!oracle9i中出现乱码!!!
- 求各位大虾帮忙看看。在线。。。。
- update set (f1,f2) = (select f1, f2 from table2 ) 如何匹配更新?
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 number SQL>