Ale S Ale S - 2 months ago 8x
SQL Question

decimal separator oracle

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
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;


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.