decimal separator oracle

Question

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"

Possible duplicate of [NLS\_NUMERIC\_CHARACTERS setting for decimal](http://stackoverflow.com/questions/24571355/nls-numeric-characters-setting-for-decimal) — Mottor, Aug 03 '16 at 09:32

score 13 · Accepted Answer · answered Aug 03 '16 at 09:31

13

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.

answered Aug 03 '16 at 09:31

Alex Poole

183,384
11
179
318

I have run into some problems with this solution. I checked the parameter NLS_NUMERIC_CHARACTERS. It's set correctly with values `',.'`. But when I try to convert string to number the decimal delimiter disappears. See: https://gist.github.com/kovalensue/52383a9d138ea39584ff854b58c6dc4e – kovalensue Feb 10 '21 at 15:59
1

@kovalensue - this question is more than four years old, so you should ask a new question if you have a follow-on issue. But the v$paramater value is `.,` not `,.`; but in any case its your session value that matters, so check what is shown in the `nls_session_parameters` view. If you do `alter session` as shown in the answer then your query gets `213174,11`. Or include the third argument, as shown. – Alex Poole Feb 10 '21 at 16:17

score 2 · Answer 2 · answered Aug 03 '16 at 09:31

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

decimal separator oracle

2 Answers2

Linked