MaMu - 6 months ago 18x

SQL Question

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`

`to_char`

`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

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.

Source (Stackoverflow)

Comments