9 blank pads with a leading space. 0 zero pads.
So 90.99 means:
Return six characters:
- A leading space
- A digit or a space
- A digit or zero if needed
- The period
- Two digits. These are zero if none is specified.
So apply this to 0 gives ' 0.00', with two blank spaces!
This is easier to see if you dump the output:
SQL> select dump(TO_CHAR(0,'90.99')) from dual;
DUMP(TO_CHAR(0,'90.99'))
-----------------------------------------------
Typ=1 Len=6: 32,32,48,46,48,48
TO_CHAR(123.0,'999.009') returns ' 123.00'Nope! It returns " 123.000":
SQL> select to_char(123.0,'999.009') from dual;
TO_CHAR(
--------
123.000
The example in the docs includes the FM modifier. This stops blank padding. Because the source doesn't have a third decimal place, it's trimmed:
SQL> select to_char(123.0,'FM999.009') from dual;
TO_CHAR(
--------
123.00
If you apply this mask to 123.006 you get all six digits though:
SQL> select to_char(123.006,'FM999.009') from dual;
TO_CHAR(
--------
123.006
0 is not a fixed-point number No. But it's the
integer part of a fixed point number. So it's saying strip out all the leading zeros, except the one in the units ("ones") column. That's why TO_CHAR(0,'9999') returns zero.
The following comparing different masks on the same input may help:
SQL> with rws as (
2 select 12.34 x from dual union all
3 select 12 x from dual union all
4 select .34 x from dual union all
5 select 0 x from dual
6 )
7 select to_char(x, '99.99') "99.99",
8 to_char(x, '90.99') "90.99",
9 to_char(x, '00.00') "00.00",
10 to_char(x, 'FM99.99') "FM99.99",
11 to_char(x, 'FM00.00') "FM00.00"
12 from rws;
99.99 90.99 00.00 FM99.9 FM00.0
------ ------ ------ ------ ------
12.34 12.34 12.34 12.34 12.34
12.00 12.00 12.00 12. 12.00
.34 0.34 00.34 .34 00.34
.00 0.00 00.00 0. 00.00