Introduction
Consider this statement and its output:
SQL> select to_number('9.9', '9G999', 'nls_numeric_characters = ,.') as n from dual;
N
-------
99
1 row selected.
It looks like I made a mistake, how did 9.9 become 99? The answer, of course, is that I forgot the correct order of the numeric characters: the decimal character is first, and the group separator is second.
OK... but the format model allows a group separator before the LAST THREE digits, and I had one, in my input string, before the LAST digit. My input string didn't match the format model exactly; why didn't I get an error?
Full disclosure: I didn't come up with this, it was asked on Stack Overflow: sql - ORACLE: Convert a string to number with a mask - Stack Overflow In that thread, I explained (speculated) that Oracle is just "being helpful", as it is in so many other situations.
My question
For comparison, consider the following statements, involving TO_DATE, input strings, and format models:
SQL> alter session set nls_date_format = 'yyyy-mm-dd';
Session altered.
SQL> column n format 999999
SQL> select to_date('12/31/2014', 'mm-dd-yyyy') as dt from dual;
DT
----------
2014-12-31
SQL> select to_date('12/31/2014', 'fxmm-dd-yyyy') as dt from dual;
select to_date('12/31/2014', 'fxmm-dd-yyyy') as dt from dual
*
ERROR at line 1:
ORA-01861: literal does not match format string
SQL> select to_date('12/31/2014', 'fxmm/dd/yyyy') as dt from dual;
DT
----------
2014-12-31
1 row selected.
As discussed in the documentation, Oracle will cut me some slack: if I follow the format model, but use slashes instead of dashes, it will "fix my input string for me".
However, if I actually want Oracle to take my format model verbatim and to throw an error if I deviate from it, I can - I just have to use the Format Exact modifier, fx.
Now for numbers, if I do something silly like in the first statement below, Oracle will cut me some slack: it will assume I meant liberal group separators, and not exactly as I put them in the format model.
As demonstrated in the Introduction, though, that prevents me from catching mistakes. I wouldn't seriously try to convert 9.9.4.3 into a number, but I may try 9.9 with the wrong decimal separator vs. group separator and I wouldn't even know, or be warned, that I magically made 9.9 into 99. It would be quite helpful if there was a Format Exact modifier for TO_NUMBER, but it seems that's only for TO_DATE.
So, my question is - is there ANY way to force verbatim interpretation of a format model in TO_NUMBER()?
SQL> select to_number('9,9,9,9', '999G999') as n from dual; -- My session's numeric characters are . for decimal
N -- and , for group separator
-------
9999 -- BAD outcome! I wanted an error to be thrown here
SQL> select to_number('9,9,9,9', 'fx999G999') as n from dual;
select to_number('9,9,9,9', 'fx999G999') as n from dual
*
ERROR at line 1:
ORA-01481: invalid number format model -- Not quite what I meant... instead of throwing out my input string,
-- my format model is invalid. I can't use fx with TO_NUMER, only with TO_DATE.