0

I have the number 123.45, but in portuguese, which is actually the locale that Oracle is currently using in our environment, this number would be 123,45, so when I try to run

SELECT TO_NUMBER('123.45') FROM DUAL;

I get ORA-06512 error.

Replace dot for comma is not an option because besides seeming a hacky solution, the oracle driver considers my java application locale and pass it on to the Oracle database session.

Is there anyway to consider both dots and commas as decimal separator in this function?

Philippe Gioseffi
  • 1,488
  • 3
  • 24
  • 41

2 Answers2

2

Use the D format model, passed as the second argument of TO_NUMBER, to represent the sessions current decimal character:

Which, if you were in America/England/etc. then you can use:

SELECT TO_NUMBER('123.45', '999D99') FROM DUAL;

And in Portugal/Germany/etc.

SELECT TO_NUMBER('123,45', '999D99') FROM DUAL;

If you want to always use . then explicitly use . in the format model:

SELECT TO_NUMBER('123.45', '999.99') FROM DUAL;

Or specify the NLS_NUMERIC_CHARACTERS setting that you want to use as the third argument to the TO_NUMBER function:

SELECT TO_NUMBER('123.45', '999D99', 'NLS_NUMERIC_CHARACTERS=''.,''') AS value FROM DUAL;

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
0

You can try REFEXP, REPLACE Function as per your version of Database. There concept is repalce . with , and hope this will help.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm

https://www.techonthenet.com/oracle/functions/regexp_replace.php

Sandeep Kumar
  • 1
  • 2
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Sumit Sharma May 19 '22 at 03:54