[FIXED] How to apply padding on the Decimal Values in Oracle?

Issue

I need help on applying padding on the Decimal values in Oracle.

E.g.:

• -36693.76 should be displayed as -000036693.76
• 367.2 should be displayed as +000000367.20

Padding should be (9,2) and sign also should be displayed.

Solution

You can use to_char() with the appropriate format mask:

TO_CHAR(the_column,'S000000000.00') 

The S indicates the position of the sign. The 0 indicates to display a 0 if no value is available for that digit (a 9 would display a blank)

The following:

with sample_data(the_column) as (
  select 367.2 from dual 
  union all
  select -36693.76 from dual
  union all
  select 1.234 from dual
  union all
  select 1.236 from dual
)
select TO_CHAR(the_column,'S000000000.00') as the_column
from sample_data;

returns:

THE_COLUMN   
-------------
+000000367.20
-000036693.76
+000000001.23
+000000001.24

Note how 1.234 got rounded to 1.23 and 1.236 to 1.24

Answered By - a_horse_with_no_name

Answer Checked By - Willingham (PHPFixing Volunteer)