Skip to Main Content

FM and zeros

NF_D99F_D90F_D00F_FM_D99F_FM_D90F_FM_D00
.9# 0.90## 0.90## 0.90##0.9##0.90##0.90#
.95# 0.95## 0.95## 0.95##0.95##0.95##0.95#
1# 1.00## 1.00## 1.00##1.##1.00##1.00#
1.05# 1.05## 1.05## 1.05##1.05##1.05##1.05#
1.1# 1.10## 1.10## 1.10##1.1##1.10##1.10#

I learned something new today about an Oracle function I have been using for almost 20 years... Never a dull moment :-)

If you use to_char like this: to_char(1, '990D99') you get "1.00", with some leading spaces.
However if you use this: to_char(1, 'fm990D99') you get "1.".

The Oracle documentation on Format Models states "FM: Fill Mode. Oracle uses trailing blank characters and leading zeroes to fill format elements to a constant width. [...] The FM modifier suppresses the above padding in the return value of the TO_CHAR function.".
I found no mention of removing trailing zeros, so still not sure why FM creates this difference.

Trailing decimal character

NF_FM_D99
1#1#

In the "FM and zeros" example above, the "fm990D99" formatting of 1 results in "1.".
To remove the decimal character from that result, you could use a dirty "nls_numeric_characters" trick - see the code below. Not recommended, but fun :-)

Code

Region

Identification
Sequence
Title
Type
Source
Source Type
Region Source

Region

Identification
Sequence
Title
Type
Source
Source Type
Region Source