I need to replace comma with point and then i need my value to be a number. So i wrote this:
select replace('12345,6789', ',' , '.') from dual --it works fine
but then I want to convert to_number that value and I get the error:
"invalid number"
The to_number() function uses the session's NLS_NUMERIC_CHARACTERS setting to decide how to interpret commas and periods. If you know your string will always have a comma as decimal separator you can override that as part of the call, using the optional third argument; although that does mean you have to specify the format model:
select to_number('12345,6789', '9999999999D9999', 'NLS_NUMERIC_CHARACTERS='',.''')
from dual;
TO_NUMBER('12345,6789','9999999999D9999
---------------------------------------
12345.6789
You don't need a separate replace() step.
You can also change the session's setting with ALTER SESSION SET NLS_NUMERIC_CHARACTERS=',.';, but you may not be able to control the setting in every client that has to run your code.
The decimal separator is defined in your locale. Here it looks like it is ,. So you need not to do the replacement before converting your string:
select to_number('12345.6789') from dual --should work already
Or change your locale:
alter session set NLS_NUMERIC_CHARACTERS= '.,';
select to_number('123'||'.'||'456') from dual;
select to_number(replace('12345,6789', ',' , '.')) from dual
