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

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

Solution 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)

Solution 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]  

Solution 3:

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

Solution 4:

Be aware when using the 'fm' syntax it will not include any values after the decimal place unless specified using zeros. For example:

SELECT TO_CHAR(12345, 'fm99,999.00') FROM dual                               

returns: '12,345.00'

SELECT TO_CHAR(12345, 'fm99,999.99') FROM dual                            

returns: '12,345.'

As you can see this would be an issue if you are expecting two zeros after the decimals place (maybe in fee reports for example).