ORACLE-TO_NUMBER函数 [英] ORACLE - TO_NUMBER Function

问题描述

我的表中有列TEXT_INT，我想使用TO_NUMBER函数转换为DECIMAL.不幸的是我得到

I have column TEXT_INT in my table and I would like convert to DECIMAL using TO_NUMBER Function. Unfortunately I am getting

INVALID NUMBER ORA-722错误.

INVALID NUMBER ORA-722 error.

我怀疑其中可能包含字符.因此，在下面的查询中发出，但TEXT_INT中没有字母.

I doubt it may have characters in it. So Issued below query but there is no alphabets in TEXT_INT.

SELECT * 
FROM NANTHA_TABLE 
WHERE UPPER(TEXT_INT) != LOWER(TEXT_INT);

请提供一个解决此问题的方法或查找错误数据的方法吗?

Would you please provide an idea to resolve this issue or ways to finding wrong data?

推荐答案

试试:

创建如下函数:

try this:

create function like this:

CREATE OR REPLACE function f_invalid_number(number_field in varchar2) return number as
  n_d number;
begin
  n_d := TO_number(number_field);
  return 0;
exception 
  when others then
    return 1;
end;
/

然后您可以像这样检查无效数据:

then you can check invalid data like this:

SELECT * 
FROM NANTHA_TABLE 
WHERE f_invalid_number(TEXT_INT) =1

这篇关于ORACLE-TO_NUMBER函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！