小数点分隔符 oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38739525/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
decimal separator oracle
提问by XLD_a
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_numberthat value and I get the error:
但是然后我想转换to_number该值并收到错误消息：
"invalid number"
"invalid number"
回答by Alex Poole
The to_number()functionuses the session's NLS_NUMERIC_CHARACTERSsetting 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:
该to_number()函数使用会话的NLS_NUMERIC_CHARACTERS设置来决定如何解释逗号和句点。如果你知道你的字符串总是有一个逗号作为小数点分隔符，你可以使用可选的第三个参数覆盖它作为调用的一部分；虽然这确实意味着您必须指定格式模型：
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.
您不需要单独的replace()步骤。
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.
您还可以使用 更改会话的设置ALTER SESSION SET NLS_NUMERIC_CHARACTERS=',.';，但您可能无法控制必须运行您的代码的每个客户端中的设置。
回答by J. Chomel
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
