title: Oracle to_char format number with fill mode (FM0000)
body: <p>I use the <code>TO_CHAR</code> function to format number from <code>0001</code> to <code>9999</code>, and to fit the column size (<code>VARCHAR2(4)</code>) where the value is inserted (even if value is > 9999).</p>
  
  <p>I use the function like this:</p>
  
  <pre><code>TO_CHAR(n, 'FM0000')
  </code></pre>
  
  <p>Examples that work:</p>
  
  <pre><code>SELECT TO_CHAR(1, 'FM0000') FROM DUAL;
  </code></pre>
  
  <p><em>Result</em>: <code>0001</code></p>
  
  <pre><code>SELECT TO_CHAR(1234, 'FM0000') FROM DUAL;
  </code></pre>
  
  <p><em>Result</em>: <code>1234</code></p>
  
  <p>But when I test <strong>with a value greater than 9999, I get an extra character</strong>:</p>
  
  <pre><code>SELECT TO_CHAR(12345, 'FM0000') FROM DUAL;
  </code></pre>
  
  <p><em>Result</em>: <code>#####</code></p>
  
  <pre><code>SELECT TO_CHAR(123456, 'FM0000') FROM DUAL;
  </code></pre>
  
  <p><em>Result</em>: <code>#####</code></p>
  
  <p><em>For information, the result I expected was <code>####</code> (on 4 chars).</em></p>
  
  <p>To sum up:</p>
  
  <ul>
  <li>When the value to convert corresponds to the expected size (4), the converted value has the same length (4)</li>
  <li>When the value to convert is longer than the expected size (5 or more), the converted value has one more character than the expected length (5).</li>
  </ul>
  
  <p><strong>How to explain this ?</strong></p>
  
  <p>I didn't found explanation in the Oracle documentation here <a href="https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i170559" rel="noreferrer">https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i170559</a></p>
  
  <p>I tried on several Oracle version (9, 10, 11) and the result is the same.</p>
  
  <p><strong>The workaround</strong> I found is to truncate the result with RPAD() function <code>RPAD(TO_CHAR(n,'FM0000'), 4)</code> but I need to understand why the TO_CHAR function is not enough.</p>