MaMu MaMu - 2 years ago 183
SQL Question

Rounding numbers to 2 digits after comma in oracle

I'm looking for any way to be able to round or trunc the numbers to 2 digits after comma. I tried with

. But didn't get what I wanted.

select round(123.5000,2) from dual;
select round(123.5000,2) from dual;

Works fine, but when I have zero as second digit after comma, I get only one 1 digit after comma in output number

select to_char(23.5000, '99.99') from dual;

Works fine, but if the number before comma has 3 digits, I'm getting '###' as output.
Apart from that I'm getting here spaces at the beginning. Is there any clear way to remove these spaces?

I'm looking a way to always get a number with two digits after comma and for all numbers(1,10,100 etc).

Answer Source

You can use the FM number format modifier to suppress the leading spaces, but note that you also then need to use .00 rather than .99, and you may want the last element of the format model before the decimal point to be a zero too if you want numbers less that 1 to be shown as, say, 0.50 instead of .50:

with t (n) as (
  select 123.5678 from dual
  union all select 123.5000 from dual
  union all select 23.5000 from dual
  union all select 0 from dual
  union all select 1 from dual
  union all select 10 from dual
  union all select 100 from dual
select n,
  round(n, 2) as n2,
  to_char(round(n, 2), '99999.99'),
  to_char(round(n, 2), 'FM99999.00') as str2,
  to_char(round(n, 2), 'FM99990.00') as str3
from t;

         N         N2 TO_CHAR(R STR2      STR3    
---------- ---------- --------- --------- ---------
  123.5678     123.57    123.57 123.57    123.57   
     123.5      123.5    123.50 123.50    123.50   
      23.5       23.5     23.50 23.50     23.50    
         0          0       .00 .00       0.00     
         1          1      1.00 1.00      1.00     
        10         10     10.00 10.00     10.00    
       100        100    100.00 100.00    100.00   

You don't strictly need the round() as well since that's the default behaviour, but it doesn't hurt to be explicit (aside from a tiny performance impact form the extra function call, perhaps).

This gives you a string, not a number. A number does not have trailing zeros. It doesn't make sense to describe an actual number in those terms. It only makes sense to have the trailing zeros when you're converting the number to a string for display.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download