“安全"TO_NUMBER()

2021-12-05 00:00:00 type-conversion oracle ora-01722 
SELECT TO_NUMBER('*') FROM DUAL

这显然给了我一个例外:

This obviously gives me an exception:

ORA-01722:无效号码

ORA-01722: invalid number

有没有办法跳过"它并获得 0 或 NULL 代替?

Is there a way to "skip" it and get 0 or NULL instead?

整个问题:我有 NVARCHAR2 字段，其中包含数字而不是几乎 ;-)(如 *)，我需要从列中选择最大的数字.

The whole issue: I have NVARCHAR2 field, which contains numbers and not almost ;-) (like *) and I need to select the biggest number from the column.

是的，我知道这是一个糟糕的设计，但这正是我现在需要的...... :-S

Yes, I know it is a terrible design, but this is what I need now... :-S

UPD:

我自己用

COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^d+')), 0)

推荐答案

我找不到比这更好的了:

I couldn't find anything better than this:

function safe_to_number(p varchar2) return number is
    v number;
  begin
    v := to_number(p);
    return v;
  exception when others then return 0;
end;

相关文章