无法求和(TO_NUMBER(varchar2字段)):ORA 01722 [ORACLE] [英] Cannot SUM(TO_NUMBER(varchar2 field)) :ORA 01722 [ORACLE]

问题描述

我将myfield作为varchar2类型，我尝试使用sum(to_number(myfield))对该字段求和，但结果是ORA-01722无效数字.

I have myfield as varchar2 type and I try to sum this field by using sum(to_number(myfield)) but the result is ORA-01722 invalid number.

在发生此错误之前，我使用了SUM(TO_NUMBER(REGEXP_REPLACE(BIKOU,'[[:alpha:]]', '')))，它可以工作，但是上周我在myfield中放入了一个十进制值，因此该代码不再起作用.

before this error occured I used SUM(TO_NUMBER(REGEXP_REPLACE(BIKOU,'[[:alpha:]]', ''))) and it works but last week I put some decimal value in myfield so this code not work anymore.

这是我在myfield中的数据示例 10,12,13.5，NULL

Here is my example of data in myfield 10,12,13.5,NULL

推荐答案

如果您从类似13.5的字符串中获取该错误，则您会话的

If you're getting that error from a string like 13.5 then your session's NLS_NUMERIC_CHARACTERS seems to be set to use a comma as the decimal separator:

alter session set nls_numeric_characters=',.';

with your_table (bikou) as (
  select '10' from dual
  union all select '12' from dual
  union all select '13.5' from dual
  union all select null from dual
)
select SUM(TO_NUMBER(REGEXP_REPLACE(BIKOU,'[[:alpha:]]', '')))
from your_table;

SQL Error: ORA-01722: invalid number

您可以将会话明确设置为使用句点作为小数点分隔符，也可以提供使用句点的格式掩码:

You can either explicitly set the session to use a period as the decimal separator, or provide a format mask that uses a period:

select SUM(TO_NUMBER(REGEXP_REPLACE(BIKOU,'[[:alpha:]]', ''), '99999999.99999'))
from your_table;

SUM(TO_NUMBER(REGEXP_REPLACE(BIKOU,'[[:
---------------------------------------
                                   35,5

或者在模型中使用小数点分隔符并覆盖会话的NLS设置:

Or use the decimal separator marker in the model and override the session's NLS setting:

select SUM(TO_NUMBER(REGEXP_REPLACE(BIKOU,'[[:alpha:]]', ''),
  '99999999D99999', 'nls_numeric_characters=''.,'''))
from your_table;

SUM(TO_NUMBER(REGEXP_REPLACE(BIKOU,'[[:
---------------------------------------
                                   35,5

显然，掩码必须适合您希望从正则表达式返回的所有值；我使用的内容可能不太适合您的数据.

The mask obviously has to be suitable for all the values you expect back from your regex; what I've used may not be quite right for your data.

这种问题就是为什么您不应该将数字或日期存储为字符串.为您的列使用正确的数据类型.

This kind of issue is why you should not store numbers or dates as strings. Use the correct data type for your columns.

这篇关于无法求和(TO_NUMBER(varchar2字段)):ORA 01722 [ORACLE]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！