Forum Stats

  • 3,824,944 Users
  • 2,260,442 Discussions


New format modifier FL - Format fLexible

Sven W.
Sven W. Member Posts: 10,535 Gold Crown
edited Jun 30, 2020 6:35AM in Database Ideas - Ideas

Currently we have two format modifiers for format masks. FX and FM
They are mostly used in the TO_CHAR function, but can also be used in other conversion functions like TO_NUMBER or TO_DATE.


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 reenabled for the portion following its third, and so on. 

The proposal is to have a new format modifier FL - FLexible format.

It should allow to use a 9 to represent as many digits (9s) as the input value has.

Its main use would be in the TO_NUMBER conversion.

Here is an example:

select to_number('1,234,567.890','FL9.9') from dual;

expected output (print format depends on sessions nls_numeric_characters setting)


Essentially it is a shorter and more flexible version of

(up to 19 digits before decimal sign, up to 19 digits after decimal symbol).

select to_number('1,234,567.890','9999999999999999999.9999999999999999999') from dual;

However this is still not flexible enough. The maximum allowed precision is 38 and we do not know if this is before or after the decimal point.

So the next two cases should also be covered by the suggestion

(up to 37 digits before decimal sign, up to 1 digit after decimal symbol).

select to_number('1,234,567.8','9999999999999999999999999999999999999.9') from dual;

(up to 1 digit before decimal sign, up to 37 digits after decimal symbol)

select to_number('1.234567890','9.9999999999999999999999999999999999999') from dual;

See also this related db idea, where Kim describes a connected problem which made me to suggest this proposal.

For the TO_CHAR format an additional improvement would be not to print the decimal sign, when it is not needed. For TO_NUMBER it already works.
So here is the current situation:

select to_number('1','9.9') from dual;

select to_char(1,'9.9') from dual; 1.0

notice the blank in front of the 1, because the space is reserved for the sign.

select to_char(1,'FM9.9') from dual;
select to_char(1,'FX9.9') from dual;ORA-01481: invalid number format model

The new FL modifier could also be used to avoid writing the period to the output string, when it is not needed.

select to_char(1,'FL9.9') from dual;

This is very similar to what TM9 currently does.

However I see one important difference and that is the usage of 0. Here is an example

select to_char(0,'TM9') from dual;0select to_char(0.1,'TM9') from dual;


select to_char(0,'FL90.9') from dual;0select to_char(0.1,'FL90.9') from dual;0.1

It returns an integer without the decimal symbol when there are no decimal places (TM9 does this already).
It returns a 0 if specified by the format mask including the decimal separator and trailing numbers (TM9 does not return a leading 0 in those cases).

Sven W.KayKKim Berg HansenWilliam RobertsonThorsten Kettner
5 votes

Active · Last Updated