CODEWITHSUNDEEP
sqloracle-databaseoracle11g
MaMu
MaMu

Reputation: 1869

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 round, trunc and to_char. 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).

Upvotes: 2

Views: 27144

Answers (4)

Alex Poole
Alex Poole

Reputation: 191425

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.

Upvotes: 5

Aleksej
Aleksej

Reputation: 22959

You can use:

select to_char(23.5000, 'fm999999999.00')
from dual

The 'fm' avoids the leading spaces, and the '00' says to always write two digits after the comma, even if they are 0. This will return a string with your format.

If you need to round the number, not only truncating the digits after the second one, you can apply the to_char to the round() of your number

Upvotes: 2

Factory Girl
Factory Girl

Reputation: 907

Try COLUMN command with FORMAT option.

COLUMN COLUMN_NAME FORMAT 99.99
SELECT COLUMN_NAME FROM TABLE_NAME ..

Upvotes: 0

rakwaht
rakwaht

Reputation: 3967

Have you tried with a cast operation?

You can try for example with this, obviously substituting 30,2 with your desired precision.

SELECT CAST (someNumber AS DECIMAL(30,2)) FROM dual

You can find the documentation of the cast operation for the oracle sql here

Upvotes: 2

Related Questions