Oracle SQL to_number中数字格式的动态长度 [英] Dynamic length on number format in to_number Oracle SQL

问题描述

我有一个表，数字以'.'形式存储为varchar2.作为小数点分隔符(例如"5.92843").

I have a table with numbers stored as varchar2 with '.' as decimal separator (e.g. '5.92843').

我想使用'，'来计算这些数字，因为这是系统默认值，并且已经使用以下to_number进行了此操作:

I want to calculate with these numbers using ',' as that is the system default and have used the following to_number to do this:

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

我的问题是，某些数字可能非常长，因为该字段是VARCHAR2(100)，并且当它长于我定义的格式时，我的to_number会失败，并显示ORA-01722.

My problem is that some numbers can be very long, as the field is VARCHAR2(100), and when it is longer than my defined format, my to_number fails with a ORA-01722.

有什么方法可以定义动态数字格式? 只要设置十进制字符，我就不太在乎格式.

Is there any way I can define a dynamic number format? I do not really care about the format as long as I can set my decimal character.

推荐答案

有什么方法可以定义无限制的数字格式?

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

唯一的方法是为整个会话范围内的nls_numeric_characters参数设置适当的值，并使用to_number()函数，而无需指定格式掩码.

The only way, is to set the appropriate value for nls_numeric_characters parameter session wide and use to_number() function without specifying a format mask.

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

Here is a simple example.Decimal separator character is comma "," and numeric literals contain period "." as decimal separator character:

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    

这篇关于Oracle SQL to_number中数字格式的动态长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！