to_number with period and comma
719076Sep 28 2009 — edited Oct 14 2009As a part of a validation function I have to validate strings against a valid number format. The following weird behaviour I have spot during the implementation.
SQL> select TO_NUMBER('1.1.1.1,11','9G999D99') from dual;
TO_NUMBER('1.1.1.1,11','9G999D99')
----------------------------------
1111,11
SQL> select TO_NUMBER('1.1.11,11','9G999D99') from dual;
TO_NUMBER('1.1.11,11','9G999D99')
---------------------------------
1111,11
SQL> select TO_NUMBER('1.111,11','9G999D99') from dual;
TO_NUMBER('1.111,11','9G999D99')
--------------------------------
1111,11
SQL> select TO_NUMBER('1111,11','9G999D99') from dual;
select TO_NUMBER('1111,11','9G999D99') from dual
*
FEHLER in Zeile 1:
ORA-01722: Ungltige Zahl
'1.111,11' should be valid only. Is it a bug or my misconception?
These statements ran on Release 11.1.0.6.0.