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

Question

In Oracle, when converting a number with a leading zero to a character, why does the leading number disappear? Is this logic Oracle specific, or specific to SQL?

Example:

SELECT TO_CHAR(0.56) FROM DUAL; /* Result = .56 */ 

Answer 1

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'), '.') correct from (   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 varchar2 as begin     return rtrim(to_char(num, 'FM999999999999990.99'), '.'); end; 

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