oracle to_char 进制转换,十进制数，to_char和Oracle

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]')

gives 0.1 and:

to_char(1, '[FORMAT_SPEC]')

gives 1.

I've tried the following solutions:

to_char(0.1)

gives '.1'.

to_char(0.1, 'FM0.099')

gives 0.1, which is okay, however:

to_char(1, 'FM0.099')

gives 1.0, which is not okay.

Do you have any suggestions?

解决方案

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

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

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

case

when instr(to_char(

), (select to_char(0, 'FMD') from dual)) = 0

then to_char(

)

else to_char(

, 'FM999990D999')

end

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