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 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 2

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]