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 :)

There doesn't seem to be any documentation, and the format model docs sort of imply it should be allowed - but it clearly isn't. I speculated about what it's doing [in this old answer](https://stackoverflow.com/a/14651312/266304). I think MoS used to give some explanation under bug 1204892 (which it said was expected behaviour, not a bug); but that seems to no longer be visible. It doesn't make sense to mix them though. Either you want to use the NLS setting or you don't. — Alex Poole, Oct 26 '22 at 08:53

score 0 · Answer 1 · answered Oct 25 '22 at 19:10

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

Oracle TO_NUMBER problem with passing format

1 Answers1