SQL Snippets ™: Tutorials for Oracle Developers

Fill Mode

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.

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 ;
 

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"
 

select
  c_date as input,
  to_char( c_date, 'YYYY' ) as without_fm ,
  to_char( c_date, 'FMYYYY' ) as with_fm
from t ;
 

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 ;
 

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"