Why is TO_CHAR adding space (one white char) at the beginning of a number?

Question

As I prepared this SQL Fiddle based on this code:

WITH l_cur AS
 (SELECT to_char(LEVEL - 1, '0000') dd FROM dual CONNECT BY LEVEL <= 10)
SELECT '''' || d1.dd || '''' o,
       '''' || d2.dd || '''' n
  FROM (SELECT dd,
               row_number() over(ORDER BY 1) rn
          FROM l_cur) d1,
       (SELECT dd,
               row_number() over(ORDER BY dbms_random.random) rn
          FROM l_cur) d2
 WHERE d1.rn = d2.rn;

O       N
------- -------
' 0000' ' 0000'
' 0001' ' 0005'
' 0002' ' 0009'
' 0003' ' 0003'
' 0004' ' 0002'
' 0005' ' 0008'
' 0006' ' 0006'
' 0007' ' 0001'
' 0008' ' 0007'
' 0009' ' 0004'
10 rows selected

To function to_char is adding one blank character ' ' (space) at beginning. Is this an Oracle bug?

Answer 1

No, it isn't a bug, it's the documented behaviour.

Negative return values automatically contain a leading negative sign and positive values automatically contain a leading space unless the format model contains the MI, S, or PR format element.

You can use a format model modifier to change this behaviour; in this case the FM 'fill mode' modifier (although the documentation doesn't really talk about its use with number format models):

SELECT to_char(LEVEL - 1, 'fm0000') ...

SQL Fiddle.