Dynamic length on number format in to_number Oracle SQL

Question

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

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 = ''.,''')

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.

Nice solution to this question: http://stackoverflow.com/a/4143834/603516 — Vadzim, Jan 15 '15 at 16:43
By chance, today I found an even simpler solution for that question (see my answer there). Sorry - this doesn't help for this problem: While 'TM9' works for TO_CHAR, it does not work for TO_NUMBER. — hvb, Jan 18 '17 at 12:41

Nick Krasnov · Accepted Answer · 2013-11-26T08:00:13.530

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

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

Thanks :). I chose to go for this solution. It also ensures that any other calculations in my procedure uses my chosen decimal separator. — Stian, Nov 26 '13 at 12:16

score 0 · Answer 2 · answered Nov 25 '13 at 15:58

0

You can't have "unlimited" number. Maximum precision is 38 significant digits. From the documentation.

answered Nov 25 '13 at 15:58

Alen Oblak

3,285
13
27

1

Alen, seems like OP talks about dynamic format mask, not *unlimited* precision. – Nick Krasnov Nov 25 '13 at 16:06

score 0 · Answer 3 · answered Nov 25 '13 at 16:33

You might try one of the following approaches (take them for an idea as I do not have a DB for trying it here):

1) Use TO_NUMBER without a format. According to Oracle docs it uses a dot for decimal separator then.

If your number contains group separators, then first remove these and convert then:

TO_NUMBER(TRANSLATE(number, ',''' ,''))

2) Generate the number format from your input:

select TO_NUMBER(n, TRANSLATE(n,' 1,234.567890',TO_CHAR(9999.9, '9G999D9')||'99999'))
from (select '9,876.54' as n from dual);

The latter translates all digits to 9, your group character (here: comma) and your decimal separator (here: dot) to those used by Oracle by default.

Dynamic length on number format in to_number Oracle SQL

3 Answers3

Linked