When using Oracle Database, you can use functions like TO_CHAR(number) to return numbers as a string, formatted to two decimal places (or however many decimal places you require).

Or you can use functions like ROUND(number) and TRUNC(number) to round or truncate the number to your required number of decimal places.

The TO_CHAR() Function

Here’s an example that uses the TO_CHAR(number) function:

SELECT TO_CHAR(1.2345, 'fm99D00')
FROM DUAL;

Result:

1.23

In this case the original number has more than two decimal places, and so the result simply truncates it to the specified number of zeros that follow the decimal/radix character (D) in the format model.

The reason I used zeros after the decimal character, is to provide trailing zeros if required.

Example:

SELECT TO_CHAR(7, 'fm99D00')
FROM DUAL;

Result:

7.00

If I don’t want the trailing zeros, I can change the 0 characters to 9s.

Here’s a comparison of the two:

SELECT 
    TO_CHAR(7, 'fm99D00') AS "r1",
    TO_CHAR(7, 'fm99D99') AS "r2"
FROM DUAL;

Result:

     r1    r2 
_______ _____ 
7.00    7.   

Using 9s in this case, resulted in a radix character, but no decimal places.

I should point out that fm is a format modifier that removes any padding that may be applied to the result. You can omit this if you don’t mind the result being padded. Although, if you do this, you might end up with trailing zeros, even when using the 9 format element.

Here’s what happens when I remove the fm from the previous example:

SELECT 
    TO_CHAR(7, '99D00') AS "r1",
    TO_CHAR(7, '99D99') AS "r2"
FROM DUAL;

Result:

       r1        r2 
_________ _________ 
  7.00      7.00   

Of course, you’re not just limited to two decimal places – you can specify more decimal places in your format model if required.

Example:

SELECT TO_CHAR(7, 'fm99D0000')
FROM DUAL;

Result:

7.0000

The ROUND() Function

Here’s an example that uses the ROUND(number) function:

SELECT ROUND(1.2573, 2)
FROM DUAL;

Result:

1.26

The ROUND() function rounds a number to a given decimal place. Here, we specified two decimal places, but we could have specified any number.

In this case, the number was rounded up.

The TRUNC() Function

Here’s an example that uses the TRUNC(number) function:

SELECT TRUNC(1.2573, 2)
FROM DUAL;

Result:

1.25

The TRUNC() function truncates a number to a given decimal place. No rounding occurs.