Oracle to_char format number with fill mode (FM0000)

Question

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

I use the function like this:

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, the result I expected was #### (on 4 chars).

To sum up:

• When the value to convert corresponds to the expected size (4), the converted value has the same length (4)
• 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).

How to explain this ?

I didn't found explanation in the Oracle documentation here https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i170559

I tried on several Oracle version (9, 10, 11) and the result is the same.

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

Answer 1

Your format model still has to allow for the sign of the value. There is no way to indicate to TO_CHAR() that it can never be negative (if that is in fact the case for your values). Even with a 4-digit number the formatting allows allows for five characters, as you can see from the column heading:

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

TO_CH
-----
1234

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

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

TO_CH
-----
-1234

Without the FM modifier you get a leading space in the returned string for positive values, so LENGTH(TO_CHAR(1234, '0000')) is 5 but LENGTH(TO_CHAR(1234, 'FM0000')) is 4, because the leading space (which normally makes the values in the column right-justified) is suppressed. With a negative value the length of the returned string is 5 either way. The format model determines that the returned data type is varchar2(5) to allow for the sign, even if you know there will never be negative values - there isn't any way for the format model to reflect that.

You can see it with positive values too if you force the sign to be shown:

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

TO_CH
-----
+1234

There isn't anything you can do about that in the TO_CHAR call. As an alternative to your RPAD workaround, you could use SUBSTR to only get 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 do have negative values you'd lose the sign:

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

SUBSTR(TO_CHAR(-
----------------
1234

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

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

RPAD(TO_CHAR(-12
----------------
-123

which also isn't good. You may not have to deal with negative numbers; but if you're dealing with number larger than you expect (i.e. you get a number >= 10000 when you're expecting only <= 9999) then I'm not sure you can be certain you won't see an (invalid?) negative number at some point too. This seems to be a data problem rather than a formatting problem, on some level anyway.

---

Based on your comment to Ollie, another approach which might be more explicit and obvious to future maintainers of the code is to spell it out in a CASE:

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

Which would also allow you to leave the string column null or use some other magic value rather than ####, if you wanted to.

And another way to trim the value, which may also be clearer, is with CAST:

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

CAST
----
####