ITPub博客

首页 > 数据库 > Oracle > 一个利用substr后转换成number出错的问题

一个利用substr后转换成number出错的问题

原创 Oracle 作者:hanjs 时间:2007-10-21 19:14:32 0 删除 编辑

此问题总结自itpub,原文链接http://www.itpub.net/873682.html

引入问题
SQL> select cashier_no,sum(substr(lpad(time,4),1,2)*60+substr(lpad(time,4),3,2)) mintus from invoice_time
where cashier 2 _no=112
group 3 by cashier_no;

CASHIER_NO MINTUS
---------- ----------
112 9117

SQL> select cashier_no,sum(substr(lpad(time,4),1,2)*60+substr(lpad(time,4),3,2)) mintus from invoice_time
2 group by cashier_no;
ERROR:
ORA-01722: invalid number

no rows selected
我想问一下,下面这条语句是那里错了??

为了分析问题,我们测试如下:

SQL> select sum(substr(lpad(5,4),1,2)*60+substr(lpad(5,4),3,2)) from dual;

select sum(substr(lpad(5,4),1,2)*60+substr(lpad(5,4),3,2)) from dual

ORA-01722: 无效数字

SQL> select sum(substr(lpad(55,4),1,2)*60+substr(lpad(55,4),3,2)) from dual;

select sum(substr(lpad(55,4),1,2)*60+substr(lpad(55,4),3,2)) from dual

ORA-01722: 无效数字

SQL> select sum(substr(lpad(555,4),1,2)*60+substr(lpad(555,4),3,2)) from dual;

SUM(SUBSTR(LPAD(555,4),1,2)*60
------------------------------
355

大家看到,在time不足3位长度,就出错了呢,那究竟是哪里的问题?让我接着做个测试。

SQL> select substr(lpad(55,4),1,2) from dual;

SUBSTR(LPAD(55,4),1,2)
----------------------

SQL> select substr(lpad(55,4),1,2)*60 from dual;

select substr(lpad(55,4),1,2)*60 from dual

ORA-01722: 无效数字

大家可以看到,原来是不足两位,在取出前两位是什么呢,肯定不是null了,验证如下

SQL> select 1 from dual where substr(lpad(55,4),1,2) is null ;

1
----------

SQL> select 1 from dual where 1=1;

1
----------
1

大家都知道lpad在默认情况下补空格的

SQL> select lpad(55,4) from dual;

LPAD(55,4)
----------
55

这样找到问题根源就知道了,可以增加处理,让lpad补'0'来处理,避免这个错误。

SQL> select sum(substr(lpad(5,4,'0'),1,2)*60+substr(lpad(5,4,'0'),3,2)) from dual;

SUM(SUBSTR(LPAD(5,4,'0'),1,2)*
------------------------------
5

如果有问题,请大家指正!

[@more@]批

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7659204/viewspace-977947/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-01-09

  • 博文量
    23
  • 访问量
    78449