Oracle TO_NUMBER problem with passing format

Question

I have the following situation.

I am trying to use TO_NUMBER as follows:

SELECT TO_NUMBER('$123,456.789','$999,999G999D999') FROM DUAL;

I get

`

ORA-01481: invalid number format model
01481. 00000 -  "invalid number format model"
*Cause:    The user is attempting to either convert a number to a string
           via TO_CHAR or a string to a number via TO_NUMBER and has
           supplied an invalid number format model parameter.
*Action:   Consult your manual.

`

I remember I've read somewhere that it's not possible to mix G and D with , and . when passing format, but can't really find any official Oracle documentation to confirm this.

If someone can clarify this situation or point me to an article or thread where I can read about it I will be very grateful, because I am unable to find anything. Maybe my googling needs a bit of help :)

Answer 1

Use G and D throughout:

SELECT TO_NUMBER('$123,456.789','$999G999G999D999') AS value FROM DUAL;

If you need to you can explicitly specify the NLS_NUMERIC_CHARACTERS setting:

SELECT TO_NUMBER(
         '$123,456.789',
         '$999G999G999D999',
         'NLS_NUMERIC_CHARACTERS=.,'
       ) AS value
FROM   DUAL;

Or use , and . throughout:

SELECT TO_NUMBER('$123,456.789','$999,999,999.999') AS value FROM DUAL;

Which all output:

VALUE
123456.789

fiddle