Decimal number, to_char, and Oracle

oracle, sql

Solution

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(<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.

Problem

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?

Original source