SQL Oracle - 用句号表示千位，用逗号表示小数点
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28152970/
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 - Format number with fullstop for thousand and comma for decimals
提问by Mistre83
I've a number like this 3225,69 and I want to convert the display to:
我有一个像 3225,69 这样的数字，我想将显示转换为：
3.225,69
Simply, I need full stop to separate thousand and comma to separate decimals:
简单地说，我需要句号来分隔千位和逗号来分隔小数：
3,69 -> 3,69
1000 -> 1.000
1500,20 -> 1.500,20
1 -> 1 (not 1,000)
Which mask should I use in to_char?
我应该在 to_char 中使用哪个掩码？
I'm my DB columns the comma means decimals.
我是我的数据库列，逗号表示小数。
回答by Alex Poole
You can use the FMformat modifierto have trailing decimal zeros blanked out:
您可以使用FM格式修饰符将尾随的十进制零清除：
select to_char(1, 'FM9G999G999D999', 'NLS_NUMERIC_CHARACTERS='',.''') from dual;
TO_CHAR(1,'FM9G999G999D999','NLS_NUMERIC_CHARACTERS='',.''')
------------------------------------------------------------
1,
But as you can see that leaves the decimal character behind; you can trim that off though:
但是正如你所看到的，这留下了小数字符；不过你可以把它剪掉：
with t as (
select 3.69 as n from dual
union all select 1000 from dual
union all select 150.20 from dual
union all select 1 from dual
union all select 0.16 from dual
)
select n,
to_char(n, '9G999G999D000') original,
to_char(n, 'FM9G999G999D999', 'NLS_NUMERIC_CHARACTERS='',.''') new,
rtrim(to_char(n, 'FM9G999G999D999', 'NLS_NUMERIC_CHARACTERS='',.'''),
',') as trimmed
from t;
N ORIGINAL NEW TRIMMED
---------- -------------- -------------- --------------
3.69 3.690 3,69 3,69
1000 1,000.000 1.000, 1.000
150.2 150.200 150,2 150,2
1 1.000 1, 1
.16 .160 ,16 ,16
I'm using the optional third NLS argument to the to_char()functionto set the G and D characters independently from my session settings.
我使用可选的第三个NLS参数的to_char()功能，从我的会话设置独立设置的G和d字符。
If you want to preserve the zero befor the decimal separator, just make the last 9before the Dinto a 0:
如果要保留小数点分隔符9之前的零，只需将最后一个放在Dinto a之前0：
with t as (
select 3.69 as n from dual
union all select 1000 from dual
union all select 150.20 from dual
union all select 1 from dual
union all select 0.16 from dual
)
select n,
to_char(n, '9G99G990D000') original,
to_char(n, 'FM9G999G990D999', 'NLS_NUMERIC_CHARACTERS='',.''') new,
rtrim(to_char(n, 'FM9G999G990D999', 'NLS_NUMERIC_CHARACTERS='',.'''),
',') as trimmed
from t;
N ORIGINAL NEW TRIMMED
---------- ------------- -------------- --------------
3.69 3.690 3,69 3,69
1000 1,000.000 1.000, 1.000
150.2 150.200 150,2 150,2
1 1.000 1, 1
.16 0.160 0,16 0,16
回答by Ahmed
you can try this
你可以试试这个
select rtrim(to_char(to_char(1, 'FM9G999G999D999', 'NLS_NUMERIC_CHARACTERS='',.'''), 'FM9G999G999D999', 'NLS_NUMERIC_CHARACTERS='',.'''),',') from dual;
I hope help you
我希望能帮助你
