Decimal number, to_char, and Oracle

Question

Hello,

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?

Answer 1

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.

Answer 2

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

Answer 3

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.