Oracle - Why does the leading zero of a number disappear when converting it TO_CHAR

I was looking for a way to format numbers without leading or trailing spaces, periods, zeros (except one leading zero for numbers less than 1 that should be present).

This is frustrating that such most usual formatting can't be easily achieved in Oracle.

Even Tom Kyte only suggested long complicated workaround like this:

case when trunc(x)=x    then to_char(x, 'FM999999999999999999')    else to_char(x, 'FM999999999999999.99')end x

But I was able to find shorter solution that mentions the value only once:

rtrim(to_char(x, 'FM999999999999990.99'), '.')

This works as expected for all possible values:

select     to_char(num, 'FM99.99') wrong_leading_period,    to_char(num, 'FM90.99') wrong_trailing_period,    rtrim(to_char(num, 'FM90.99'), '.') correctfrom (  select num from (select 0.25 c1, 0.1 c2, 1.2 c3, 13 c4, -70 c5 from dual)  unpivot (num for dummy in (c1, c2, c3, c4, c5))) sampledata;    | WRONG_LEADING_PERIOD | WRONG_TRAILING_PERIOD | CORRECT |    |----------------------|-----------------------|---------|    |                  .25 |                  0.25 |    0.25 |    |                   .1 |                   0.1 |     0.1 |    |                  1.2 |                   1.2 |     1.2 |    |                  13. |                   13. |      13 |    |                 -70. |                  -70. |     -70 |

Still looking for even shorter solution.

There is a shortening approarch with custom helper function:

create or replace function str(num in number) return varchar2asbegin    return rtrim(to_char(num, 'FM999999999999990.99'), '.');end;

But custom pl/sql functions have significant performace overhead that is not suitable for heavy queries.

It's the default formatting that Oracle provides. If you want leading zeros on output, you'll need to explicitly provide the format. Use:

SELECT TO_CHAR(0.56,'0.99') FROM DUAL;

or even:

SELECT TO_CHAR(.56,'0.99') FROM DUAL;

The same is true for trailing zeros:

SQL> SELECT TO_CHAR(.56,'0.990') val FROM DUAL;VAL------ 0.560

The general form of the TO_CHAR conversion function is:

TO_CHAR(number, format)

Seems like the only way to get decimal in a pretty (for me) form requires some ridiculous code.

The only solution I got so far:

CASE WHEN xy>0 and xy<1 then '0' || to_char(xy) else to_char(xy)

xy is a decimal.

xy             query result0.8            0.8  --not sth like .8010             10  --not sth like 10.00