Decimal number, to_char, and 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 suggestio开发者_开发技巧ns?

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.

I just use this:

TRIM('.' FROM TO_CHAR(x, 'FM99990.999'))

Don't happen to have an Oracle instance handy to test this in, but I'd think that

TO_CHAR(1, 'FM0.999')

oughta do it.

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:

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