十进制数、to_char 和 Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2033756/
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 number, to_char, and Oracle
提问by Ariod
I am trying to figure out a format spec of to_char() that would give me the following result.
我试图找出 to_char() 的格式规范，它会给我以下结果。
to_char(0.1, '[FORMAT_SPEC]')
gives 0.1 and:
给出 0.1 并且：
to_char(1, '[FORMAT_SPEC]')
gives 1.
给 1。
I've tried the following solutions:
我尝试了以下解决方案：
to_char(0.1)
gives '.1'.
给出'.1'。
to_char(0.1, 'FM0.099')
gives 0.1, which is okay, however:
给出 0.1，这是可以的，但是：
to_char(1, 'FM0.099')
gives 1.0, which is not okay.
给出 1.0，这是不行的。
Do you have any suggestions?
你有什么建议吗？
回答by OMG Ponies
The precision returned needs to be consistent, so the only alternative is to use DECODEor CASEstatements to conditionally return what you need:
返回的精度需要一致，因此唯一的选择是使用DECODE或CASE语句有条件地返回您需要的内容：
CASE
WHEN INSTR(TO_CHAR(t.col), '.') = 0 THEN TO_CHAR(t.col)
ELSE TO_CHAR(t.col, 'FM0.099')
END
The example isn't great - it's not clear if your data will have values like 1.000or values above one/etc.
这个例子不是很好 - 不清楚您的数据是否具有类似1.000或高于 1 的值/等。
EDIT Michael-O (2013-06-25): For those who need it idiot-proof, you may try:
编辑 Michael-O (2013-06-25)：对于那些需要防白痴的人，您可以尝试：
case
when instr(to_char(<col>), (select to_char(0, 'FMD') from dual)) = 0
then to_char(<col>)
else to_char(<col>, 'FM999990D999')
end
It automatically observes the decimal separator. Adapt the the secodn format modal to your number size.
它会自动观察小数点分隔符。根据您的数字大小调整 secodn 格式模式。
回答by Jeffrey Kemp
I just use this:
我只是用这个：
TRIM('.' FROM TO_CHAR(x, 'FM99990.999'))
回答by Bob Jarvis - Reinstate Monica
Don't happen to have an Oracle instance handy to test this in, but I'd think that
碰巧手边没有 Oracle 实例来测试这个，但我认为
TO_CHAR(1, 'FM0.999')
oughta do it.
应该做
回答by Doug Porter
Not sure what range of values you will be expecting but you could case out values < 1 versus those >= 1. Otherwise either the trailing 0 or the decimal is going to get in your way:
不确定您期望的值范围是多少，但您可以将值 < 1 与那些 >= 1 区分。否则尾随的 0 或小数会妨碍您：
select val,
case when val < 1 then to_char(val, 'FM99990.9')
else to_char(val, 'FM99999')
end fmt
from (select 0.1 val from dual union all
select 1 from dual
)
/
VAL FMT
---------- --------
.1 0.1
1 1
