Why is Oracle's to_char() function adding spaces?

Question

Why is Oracle's to_char() function adding spaces?

select length('012'), length(to_char('012')), length(to_char('12', '000')) from dual

3 , 3, 4

Answer 1

The format mask that you are using is fixed width and allows fo a minus sign

Answer 2

The extra leading space is for the potential minus sign. To remove the space you can use FM in the format:

SQL> select to_char(12,'FM000') from dual;

TO_C
----
012

By the way, note that to_char takes a NUMBER argument; to_char('012') is implicitly converted to to_char(to_number('012')) = to_char(12)

Answer 3

To make the answers given more clear:

select '['||to_char(12, '000')||']', 
       '['||to_char(-12, '000')||']', 
       '['||to_char(12,'FM000')||']' 
from dual


[ 012]                      [-012]                       [012]