sql - to_number Oracle SQL 中数字格式的动态长度

Question

我有一个表格，其中的数字存储为 varchar2 和“.”作为小数点分隔符(例如“5.92843”)。

我想使用“,”来计算这些数字，因为这是系统默认设置，并使用了以下 to_number 来执行此操作:

TO_NUMBER(number,'99999D9999','NLS_NUMERIC_CHARACTERS = ''.,''')

我的问题是有些数字可能很长，因为字段是 VARCHAR2(100)，当它比我定义的格式长时，我的 to_number 失败带有 ORA-01722。

有什么方法可以定义动态数字格式？只要我可以设置我的十进制字符，我就不太关心格式。

Answer 1

Is there any way I can define an unlimited number format?

唯一的方法是在 session 范围内为nls_numeric_characters 参数设置适当的值，并在不指定格式掩码的情况下使用to_number() 函数。

这是一个简单的例子。小数分隔符是逗号","，数字文字包含句点"."作为小数分隔符:

SQL> show parameter nls_numeric_characters;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
nls_numeric_characters               string      ,.

SQL> with t1(col) as(
  2    select '12345.567'  from dual union all
  3    select '12.45'      from dual
  4  )
  5  select to_number(col) as res
  6    from t1;
select to_number(col)
       *
ERROR at line 5:
ORA-01722: invalid number 

SQL> alter session set nls_numeric_characters='.,';

Session altered.

SQL> with t1(col) as(
  2    select '12345.567'  from dual union all
  3    select '12.45'      from dual
  4  )
  5  select to_number(col) as res
  6    from t1;

res                                                                 
--------------    
     12345.567       
         12.45