Format Model Modifiers
The FM and FX modifiers, used in format models in the TO_CHAR function, control blank padding and exact format
checking.
A modifier can appear in a format model more than once. In such a case, each subsequent occurrence toggles the
effects of the modifier. Its effects are enabled for the portion of the model following its first occurrence, and then
disabled for the portion following its second, and then re-enabled for the portion following its third, and so on.
FM - Using the TO_CHAR Function with Dates and Numbers
"Fill mode". This modifier suppresses blank padding in the return value of the TO_CHAR function:
In a datetime format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character
elements (such as MONTH) and suppresses leading zeroes for subsequent number elements (such as MI) in a date
format model. Without FM, the result of a character element is always right padded with blanks to a fixed
length, and leading zeroes are always returned for a number element. With FM, because there is no blank
padding, the length of the return value may vary.
In a number format element of a TO_CHAR function, this modifier suppresses blanks added to the left of the
number, so that the result is left-justified in the output buffer. Without FM, the result is always right-justified
in the buffer, resulting in blank-padding to the left of the number.
FX - Using the TO_DATE Function with Character Strings
"Format eXact". This modifier specifies exact matching for the character argument and date format model of
a TO_DATE function:
Punctuation and quoted text in the character argument must exactly match (except for case) 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.
SELECT TO_DATE('15/JAN/1998', 'FXDD-MON-YYYY'), --Error
TO_DATE('15-JAN-1998', 'FXDD-MON-YYYY'), --Match
TO_DATE('1-JAN-1998', 'FXDD-MON-YYYY'), --Error
TO_DATE('01-JAN-1998', 'FXDD-MON-YYYY'), --Match
TO_DATE('1-JAN-1998', 'FXFMDD-MON-YYYY') --Match (啟用 FX 時，您也可以使用 FM 修改器來禁用前導零的檢查。)
FROM DUAL;
If any portion of the character argument violates any of these conditions, then Oracle returns an error message.
Dates and Times
Fill Mode Formatting with the FM Modifier
The Fill Mode modifier, FM, toggles the suppression of blank padding in character format elements like
"MONTH" and leading zeros in numeric format elements like "YYYY". Here are some examples.
Example 1:
COLUMN without_fm FORMAT a15
COLUMN with_fm FORMAT a15
SELECT c_date AS input,
'"' || to_char(c_date, 'MONTH') || '"' AS without_fm,
'"' || to_char(c_date, 'FMMONTH') || '"' AS with_fm
FROM t;
Output:
INPUT WITHOUT_FM WITH_FM
----------- --------------- ---------------
-2400-10-31 "OCTOBER " "OCTOBER"
-0500-02-28 "FEBRUARY " "FEBRUARY"
-0001-12-31 "DECEMBER " "DECEMBER"
0000-01-01 "000000000" "000000000"
0001-06-21 "JUNE " "JUNE"
0820-11-23 "NOVEMBER " "NOVEMBER"
1947-12-16 "DECEMBER " "DECEMBER"
2007-06-15 "JUNE " "JUNE"
Example 2:
COLUMN without_fm FORMAT a15
COLUMN with_fm FORMAT a15
SELECT c_date AS input,
to_char(c_date, 'YYYY') AS without_fm,
to_char(c_date, 'FMYYYY') AS with_fm
FROM t;
Output:
INPUT WITHOUT_FM WITH_FM
----------- --------------- ---------------
-2400-10-31 2400 2400
-0500-02-28 0500 500
-0001-12-31 0001 1
0000-01-01 0000 0000
0001-06-21 0001 1
0820-11-23 0820 820
1947-12-16 1947 1947
2007-06-15 2007 2007
FM will not, however, suppress blanks in number elements.
SELECT c_date,
'"' || to_char(c_date, 'SYYYY') || '"' AS without_fm,
'"' || to_char(c_date, 'FMSYYYY') || '"' AS with_fm
FROM t;
Output:
C_DATE WITHOUT_FM WITH_FM
----------- --------------- ---------------
-2400-10-31 "-2400" "-2400"
-0500-02-28 "-0500" "-500"
-0001-12-31 "-0001" "-1"
0000-01-01 "00000" "00000"
0001-06-21 " 0001" " 1"
0820-11-23 " 0820" " 820"
1947-12-16 " 1947" " 1947"
2007-06-15 " 2007" " 2007"
Exact Formatting with the FX Modifier
The Format eXact modifier, FX, toggles exact format matching on or off. It is used only in input format models
(e.g. with TO_DATE). It has no effect when used in output format models (e.g. with TO_CHAR). When toggled
on, FX forces the function to perform an exact match between the format model and the character argument. For
example, these values work without error when FX is not specified.
Example:
COLUMN b FORMAT a20
COLUMN c FORMAT a30
SELECT to_date('123', 'yyyy') AS a, -- three digit year, not four
to_timestamp('2007 12', 'yyyy mm') AS b, -- extra spaces
to_timestamp_tz('2007/12', 'yyyy-mm') AS c -- "/" instead of "-"
FROM dual;
Output:
A B C
----------- -------------------- ------------------------------
0123-06-01 2007-12-01 00:00:00 2007-12-01 00:00:00 -04:00
While the same values will generate errors when FX is specified.
SELECT to_date('123', 'FXyyyy') AS a FROM dual;
SELECT to_date('123', 'FXyyyy') AS a FROM dual
ERROR at line 1:
ORA-01862: the numeric value does not match the length of the format item
SELECT to_timestamp('2007 12', 'FXyyyy mm') AS b FROM dual;
SELECT to_timestamp('2007 12', 'FXyyyy mm') AS b FROM dual
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
SELECT to_timestamp_tz('2007/12', 'FXyyyy-mm') AS c FROM dual;
SELECT to_timestamp_tz('2007/12', 'FXyyyy-mm') AS c FROM dual
ERROR at line 1:
ORA-01861: literal does not match format string
