Oracle SQL display 2 decimaldigits

Question

How can I display 2 decimal digits for an integer? The column I wish to display contains both int and float

For example: 3000 display as 3000.00; 235.5 display as 235.50; 123.126 dispaly as 123.12

Answer 1

One option is to use to_char function with desired format mask. For example:

SQL> with test (col) as
  2    (select 3000    from dual union all
  3     select 235.5   from dual union all
  4     select 123.126 from dual
  5    )
  6  select col,
  7         to_char(col, '999g999g990d00') result
  8  from test;

       COL RESULT
---------- ---------------
      3000        3.000,00
     235,5          235,50
   123,126          123,13

SQL>

---

Another is to use set numformat:

SQL> set numformat 999g999g990d00
SQL> with test (col) as
  2    (select 3000   from dual union all
  3     select 235.5  from dual union all
  4     select 123.126 from dual
  5    )
  6  select col
  7  from test;

            COL
---------------
       3.000,00
         235,50
         123,13

SQL>

(In SQL Developer you might need to use e.g. set numformat 999,999,990.00 because previously suggested option might raise SP2-0246: illegal FORMAT string error).