Oracle SQL - 更改逗号的十进制点
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12974856/
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
Oracle SQL - change decimal dot for a comma
提问by Andre Cardoso
I'm on Brazil, and our currency format is something like 'R$ 9.999,00'.
我在巴西，我们的货币格式类似于“R$ 9.999,00”。
I'm trying to select a field, and change the format of the return. However, it seems I can't do it. I've tried:
我正在尝试选择一个字段，并更改返回的格式。不过，我好像做不到。我试过了：
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';
and
和
SELECT to_char(10,'9,999.00', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL
None of those worked. Not sure if it might be my client (I'm on Ubuntu using sqldeveloper), or if I'm doing something wrong.
这些都没有用。不确定它是否可能是我的客户（我使用 sqldeveloper 在 Ubuntu 上），或者我做错了什么。
Any ideas?
有任何想法吗？
回答by Petr Pribyl
Use
用
SELECT to_char(10,'9G990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL
G is symbol for thousands separator D is for decimal separator
G 是千位分隔符 D 是十进制分隔符
回答by Josef Procházka
I think that your format mask is wrong.Please try this format mask:
我认为您的格式掩码有误。请尝试使用此格式掩码：
SELECT to_char(10000,'99G990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL
make sure you will use correct number of leading '9' in format mask for bigger numbers
确保您将在格式掩码中使用正确数量的前导“9”以获得更大的数字
回答by ddsultan
I solved the issue by:
我通过以下方式解决了这个问题：
SELECT TO_CHAR(1000000000,'999G999G999G999D99','NLS_NUMERIC_CHARACTERS = '',.''') FROM DUAL;
to format a number by billions. I think it helps.
以十亿为单位格式化一个数字。我认为它有帮助。
回答by user7708231
CREATE OR REPLACE FUNCTION APPS.EX_number_format( ff number) RETURN char IS
xx varchar2(100);
yy varchar2(1);
tt varchar2(100);
x number:=0;
begin
xx:=to_char(ff,'99G990G990G990G990G990D00');
for i in 1..length(xx) loop
yy:=substr(xx,i,1);
if yy in ('1','2','3','4','5','6','7','8','9') then
x:=i;
exit;
end if;
end loop;
tt:=substr(xx,x,length(xx));
return tt;
END EX_number_format;
select EX_number_format(1000) from dual;
从双中选择 EX_number_format(1000);
1,000.00
1,000.00
select EX_number_format(1859684500) from dual;
从双中选择 EX_number_format(1859684500)；
1,859,684,500.00
1,859,684,500.00
