Oracle 11.2 to_number multiple commas

Question

In Oracle 11.2, is there some number format, nf, that will work with to_number to parse arbitrary length varchar2s containing digits and commas?

I can achieve this without a number format, by using regexp_replace, but I'd prefer to achieve the same thing using just a number format.

e.g., the following 2 statements work:

select to_number(regexp_replace('12,345', ',', '')) from dual;

select to_number(regexp_replace('1,234,567', ',', '')) from dual;

but I'd prefer:

select to_number('12,345', nf) from dual;

select to_number('1,234,567', nf) from dual;

where nf is one number format string that works for both statements.

If I try nf = '99,999', the first statement works, but the second fails.

Thanks.

score 2 · Answer 1 · answered Feb 12 '13 at 10:04

Oracle won't complain if the number format is too long, so you can use a model that has enough digits to cope with the biggest number you can receive:

SQL> select to_number('12,345',
  2  '999G999G999G999G999G999G999G999G999G999G999G999G999') from dual;


TO_NUMBER('12,345','999G999G999G999G999G999G999G999G999G999G999G999G999')
-------------------------------------------------------------------------
                                                                    12345

SQL> select to_number('1,234,567',
  2  '999G999G999G999G999G999G999G999G999G999G999G999G999') from dual;

TO_NUMBER('1,234,567','999G999G999G999G999G999G999G999G999G999G999G999G999')
----------------------------------------------------------------------------
                                                                     1234567

SQL> select to_number('999,999,999,999,999,999,999,999,999,999,999,999,999',
  2  '999G999G999G999G999G999G999G999G999G999G999G999G999') from dual;

TO_NUMBER('999,999,999,999,999,999,999,999,999,999,999,999,999','999G999G999G999
--------------------------------------------------------------------------------
                                                                      1.0000E+39

I've used the G group separator instead of a fixed comma to support globalisation, but the effect is the same.

The only caveat is that the source number has to have the right grouping so it matches the formatting exactly for the digits it does have:

SQL> select to_number('1,2345',
  2  '999G999G999G999G999G999G999G999G999G999G999G999G999') from dual;

select to_number('1,2345',
                 *
ERROR at line 1:
ORA-01722: invalid number

Alternative shorter solution for strings of any supported length: http://stackoverflow.com/a/4143834/603516 — Vadzim, Jan 15 '15 at 16:44
@Vadzim - funnily enough I've just reinvented that and was about to add it to this answer. But now I see I've already upvoted Vincent's answer, so maybe it's been lurking at the back of my brain... Maybe significant differences are that `translate` will work with the last example I gave since the separator positions aren't fixed, and it'll work with decimals. Thanks. — Alex Poole, Jan 15 '15 at 16:49

score 0 · Answer 2 · answered Feb 12 '13 at 07:30

0

For this :

select to_number('1,234,567', nf) from dual;

Use nf = 9,999,999 will work.

answered Feb 12 '13 at 07:30

Iswanto San

18,263
13
58
79

Ben · Accepted Answer · 2013-02-12T22:21:48.540

Although I support Alex Poole's answer, here's another crude but effective way of solving the problem that should perform better than doing a regex.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_of_numbers (
  example_num VARCHAR2(50)
)
/

INSERT INTO table_of_numbers (example_num)
VALUES ('12,345')
/

INSERT INTO table_of_numbers (example_num)
VALUES ('1,234,567')
/

Query 1:

SELECT TO_NUMBER(example_num, RPAD('9', LENGTH(example_num) - 1, '9')) fudge
FROM table_of_numbers

Results:

|   FUDGE |
-----------
|   12345 |
| 1234567 |

If you need to match the commas, then you could do something slightly more sophisticated with INSTR and LPAD to make sure you generate the right mask.

Oracle 11.2 to_number multiple commas

3 Answers3