Format Model Modifiers - FX and FM

The FM and FX modifiers are used in the TO_CHAR function in oracle sql in the format model. These two modifiers control blank padding and exact format checking.

FM:
FM is fill mode. If we simply use TO_CHAR function that is without FM then Oracle uses blank characters to fill format elements to a constant width equal to the largest element for the relevant format model in the current session language. For example if NLS_LANGUAGE is set to AMERICAN then, in case of month the highest length month is SEPTEMBER. So if we don't use FM modifier then every month takes the length of 9 characters. (that is month SEPTEMBER). But if we use FM format model modifiers then the blank characters are suppressed.

Below example will clear you.

SQL> select to_char(SYSDATE,'fmDDTH MONTH YYYY') from dual;

TO_CHAR(SYSDATE,'FM
-------------------
24TH AUGUST 2009

SQL> select to_char(SYSDATE,'DDTH MONTH YYYY') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
24TH AUGUST    2009

SQL> show parameter nls_language

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
nls_language                         string      AMERICAN

SQL> select to_char(SYSDATE,'Day')||' Time'  from dual;

TO_CHAR(SYSDAT
--------------
Monday    Time
SQL> select to_char(SYSDATE,'fmDay')||' Time'  from dual;

TO_CHAR(SYSDAT
--------------
Monday Time

FX:
FX is format exact.
FX modifier specifies exact matching for the character argument and datetime format model of a TO_DATE function.

Let's have a look at FX format model specification.
- Punctuation and quoted text in the character argument must exactly match the corresponding parts of the format model.

- The character argument cannot have extra blanks. Without FX, Oracle ignores
extra blanks.

- Numeric data in the character argument must have the same number of digits as
the corresponding element in the format model. Without FX, numbers in the
character argument can omit leading zeroes.

When FX is enabled, you can disable this check for leading zeroes by using the FM
modifier as well.

If any portion of the character argument violates any of these conditions, then Oracle returns an error message.