Oracle – Understanding TO_CHAR Format ‘FM99990.90’

oracle

I have this number format and not able to find examples of how it will look. It is not listed on Oracle website. This below SQL is what I have and not sure what the result will look like since I don't have access to Oracle SQL at the moment to be able to run and see the result.

to_char(deductionAmount, 'FM99990.90')

Does anyone know? Deduction amount is in this format most likely: xx.xx

Best Answer

Of course it is listed/documented.

Format Model Modifiers

FM

Fill mode. Oracle uses trailing blank characters and leading zeroes to fill format elements to a constant width. The width is equal to the display width of the largest element for the relevant format model:

Numeric elements are padded with leading zeros to the width of the maximum value allowed for the element. For example, the YYYY element is padded to four digits (the length of '9999'), HH24 to two digits (the length of '23'), and DDD to three digits (the length of '366').

...

Example:

SQL> select to_char(1.5, 'FM99990.90') from dual;

TO_CHAR(1
---------
1.50

SQL> select to_char(1.5, '99990.90') from dual;

TO_CHAR(1
---------
     1.50

SQL>

Related Solutions

Oracle build_plan_xml visualizer

You can use dbms_xplan.display_plan to get text, xml, html or 'active' output as a clob:

select dbms_xplan.display_plan(format=>'ALL', type=>'HTML') from dual;

select dbms_xplan.display_plan(format=>'ALL', type=>'ACTIVE') from dual;

Html is similar to the text output, but the 'active' type is quite different:

Active reports have a rich, interactive user interface akin to that found in Enterprise Manager while not requiring any EM installation. The report file built is in HTML format, so it can be interpreted by most modern browsers. The code powering the active report is downloaded transparently by the web browser when the report is first viewed, hence viewing it requires outside connectivity.

Zero conversion in sql

It's happens because s specifier makes presence of sign mandatory and places it on specified position. Even you can put sign as last symbol in string, look at number format documentation or just try:

select
  to_char(10,'S999')  "10 S999",
  to_char(-10,'S999') "-10 S999",
  to_char(0,'S999')   "0 S999",
  to_char(10,'999S')  "10 999S",
  to_char(-10,'999S') "-10 999S",
  to_char(0,'999S')   "0 999S"
from dual

SQLFiddle

According to documentation:

Negative return values automatically contain a leading negative sign and positive values automatically contain a leading space unless the format model contains the MI, S, or PR format element.

you always get a "minus" char for negative values at start of a string, so there are no need for S format specifier at most of the cases.
But if you need for some purpose to always place a sign before or after digits, then you can use S format specifier.

If you ask "why +0 and not -0" - it's just "by design" :)

Update
And some words about "design":

A long time ago (far far away ... :) ) Oracle decide to use special values of data types to indicate null values. For character data it's empty string (that's also source of two string types - varchar and varchar2) and for numeric types it's -0 value.

If you look at specification of computer number format you can find, that for a signed number one bit always reserved to store sign. Zero value of this bit treated as '+' and value 1 as -.
Two representations of zero -0 and +0 are same from the point of view of math and one of them can be eliminated without loosing any functionality. So, Oracle designers decide to always store zero value as +0 and treat -0 as null.
May be behavior changed in modern versions of Oracle, but for compatibility reasons zero value normalization remains unchanged till today and you always got +0 in string representation.

Best Answer

Related Solutions

Oracle build_plan_xml visualizer

Zero conversion in sql

Related Question