Oracle to_char format number with fill mode (FM0000)

Question

I use the TO_CHAR function to format a number from 0001 to 9999 and to match the size of the column ( VARCHAR2(4) ) where the value is inserted (even if the value is> 9999).

I use the function as follows:

 TO_CHAR(n, 'FM0000') 

Examples that work:

 SELECT TO_CHAR(1, 'FM0000') FROM DUAL; 

Result: 0001

 SELECT TO_CHAR(1234, 'FM0000') FROM DUAL; 

Result: 1234

But when I test with a value greater than 9999, I get an extra character :

 SELECT TO_CHAR(12345, 'FM0000') FROM DUAL; 

Result: #####

 SELECT TO_CHAR(123456, 'FM0000') FROM DUAL; 

Result: #####

For information, I expected the result #### (4 characters).

Summarizing:

• When the value for the conversion corresponds to the expected size (4), the converted value has the same length (4)
• When the value for the conversion is larger than the expected size (5 or more), the converted value has one more character than the expected length (5).

How to explain this?

I did not find an explanation in the Oracle documentation here https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i170559

I tried using several versions of Oracle (9, 10, 11), and the result will be the same.

The workaround I found is truncating the result using the RPAD () RPAD(TO_CHAR(n,'FM0000'), 4) , but I need to understand why the TO_CHAR function is not enough.

Answer 1

Your format model should still consider the sign of the value. It is not possible to tell TO_CHAR() that it can never be negative (if this is true for your values). Even with a 4-digit number that allows formatting, five characters are allowed, as you can see from the column heading:

 SQL> SELECT TO_CHAR(1234, 'FM0000') FROM DUAL; TO_CH ----- 1234 

Note that the column heading is TO_CH , which is five characters, not four. If you have a negative number (as Florin suggested), you need extra space:

 SQL> SELECT TO_CHAR(-1234, 'FM0000') FROM DUAL; TO_CH ----- -1234 

Without the FM modifier, you get the leading space in the returned string for positive values, so LENGTH(TO_CHAR(1234, '0000')) is 5, and LENGTH(TO_CHAR(1234, 'FM0000')) is 4, because the leading space (which usually makes the values ​​in the column right- justified) suppressed. If the value is negative, the length of the returned string is 5 in any case. The format model determines that the returned data type is varchar2(5) to allow the sign, even if you know that there will never be negative values ​​- there will be no reflection for the format model.

You can also see it with positive values ​​if you force the sign to be displayed:

 SQL> SELECT TO_CHAR(1234, 'FMS0000') FROM DUAL; TO_CH ----- +1234 

There is nothing you can do in the TO_CHAR call. As an alternative to the RPAD you can use SUBSTR to get only the last four characters of the formatted string:

 SQL> SELECT SUBSTR(TO_CHAR(12345, 'FM0000'), -4) FROM DUAL SUBSTR(TO_CHAR(1 ---------------- #### 

But if you have negative values, you will lose the sign:

 SQL> SELECT SUBSTR(TO_CHAR(-1234, 'FM0000'), -4) FROM DUAL SUBSTR(TO_CHAR(- ---------------- 1234 

With your RPAD, you retain the sign, but lose the fourth significant digit:

 SQL> SELECT RPAD(TO_CHAR(-1234, 'FM0000'), 4) FROM DUAL RPAD(TO_CHAR(-12 ---------------- -123 

which is also bad. You may not have to deal with negative numbers; but if you are dealing with a number larger than you expect (i.e. you get the number> = 10000 when you only expect <= 9999), then I’m not sure that you can be sure you won’t see (invalid?) negative number at some point too. In any case, this is a data problem, not a formatting problem at any level.

---

Based on your comment on Ollie, another approach that may be more explicit and obvious to future code developers is to describe it in CASE:

 SELECT CASE WHEN n BETWEEN 0 AND 9999 THEN TO_CHAR(n, 'FM0000') ELSE '####' END FROM DUAL 

Which will also allow you to leave the row zero column or use another magic value, not #### if you want.

And another way to crop a value, which could also be clearer, is with CAST:

 SQL> SELECT CAST(TO_CHAR(12345, 'FM0000') AS VARCHAR2(4)) FROM DUAL; CAST ---- ####