十进制数，to_char和Oracle [英] Decimal number, to_char, and Oracle

问题描述

我正在尝试找出to_char()的格式规范，这将给我以下结果.

I am trying to figure out a format spec of to_char() that would give me the following result.

to_char(0.1, '[FORMAT_SPEC]')

给出0.1并且:

to_char(1, '[FORMAT_SPEC]')

给予1.

我尝试了以下解决方案:

I've tried the following solutions:

to_char(0.1)

给出'.1'.

to_char(0.1, 'FM0.099')

给出0.1，可以，但是:

gives 0.1, which is okay, however:

to_char(1, 'FM0.099')

给出1.0，这不行.

您有什么建议吗?

推荐答案

返回的精度必须保持一致，因此唯一的选择是使用 CASE 语句有条件地返回您需要的东西:

The precision returned needs to be consistent, so the only alternative is to use DECODE or CASE statements to conditionally return what you need:

CASE 
  WHEN INSTR(TO_CHAR(t.col), '.') = 0 THEN TO_CHAR(t.col)
  ELSE TO_CHAR(t.col, 'FM0.099')
END

该示例不是很好-尚不清楚您的数据是否将具有1.000之类的值或高于//etc的值.

The example isn't great - it's not clear if your data will have values like 1.000 or values above one/etc.

编辑Michael-O(2013-06-25):对于那些需要防白痴的人，您可以尝试:

EDIT Michael-O (2013-06-25): For those who need it idiot-proof, you may try:

case
  when instr(to_char(<col>), (select to_char(0, 'FMD') from dual))  = 0
    then to_char(<col>) 
  else to_char(<col>, 'FM999990D999')
end

它会自动观察小数点分隔符.调整secodn格式模式以适合您的数字大小.

It automatically observes the decimal separator. Adapt the the secodn format modal to your number size.

这篇关于十进制数，to_char和Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！