时间戳

首页 > 解决方案 > 为什么 Oracle 不将字符转换为数字？

sql - 为什么 Oracle 不将字符转换为数字？

问题描述

Oracle 抛出ORA-01722: invalid number了我的 SQL 查询，目前还不清楚原因。

我有一个名为“LIGHTS”的表，我想获得 WATTAGE <= 3 的灯。由于某种原因，WATTAGE 存储为 VARCHAR2(40)，但每个字符似乎都是整数或浮点数。当我使用查询将 WATTAGE 转换为数字时：

SELECT TO_NUMBER(WATTAGE) FROM LIGHTS

这里没有问题。我得到这样的结果：

TO_NUMBER(WATTAGE) 
1
7
-1
0
15
17.5

但是，当我添加 WHERE 条件来过滤小于 3 的数字时，ORA-01722: invalid number出现错误：

SELECT WATTAGE FROM LIGHTS
WHERE TO_NUMBER(WATTAGE) <= 3

可能出了什么问题？

标签： sqloracle

解决方案

唉，Oracle 没有一种简单的方法来检查一个字符串是否实际上代表一个数字。（首先使用正确数据类型的众多原因之一！）

但是，您可以自己编写。这里只是这个概念的一个简短的演示。我创建了一个包含 VARCHAR2 数据类型列的表，并用几个字符串填充它，其中一个不是数字。

create table tbl (nbr varchar2(100));
insert into tbl
  select '103'  from dual union all
  select '-1.3' from dual union all
  select 'abc'  from dual
;

然后我创建一个带有嵌套块的小函数，如果TO_NUMBER失败应该出错。错误处理程序将针对错误“做一些事情”，然后将控制权返回给主函数。然后我可以在 WHERE 子句中使用它。以下是函数以及如何使用它来查找违规值：

create or replace function not_a_number(str varchar2)
return varchar2
as
  x number;
  r varchar2(100);
begin
  begin
    x := to_number(str);
  exception
    when others then
    r := str;
  end;
  return r;
end;
/

select nbr
from   tbl
where  not_a_number(nbr) is not null;

NBR 
-------
abc

推荐阅读