When using Oracle Database, you can use functions like TO_CHAR(number) to return numbers as a string. This enables you to format the number to have a specific number of decimal places – including no decimal places, if that’s what you want.

You can also use functions like ROUND(number) and TRUNC(number) to round or truncate the number to no decimal places.

There’s also the CAST() function that allows you to cast a data type as an integer, which effectively removes any decimal places.

The TO_CHAR() Function

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

SELECT TO_CHAR(1234.56, 'fm99G999')
FROM DUAL;

Result:

1,235

In this case my format model consists of:

• one fm, which is a format modifier that removes any leading and/or trailing padding characters that might be added to the result.
• multiple 9 characters, which represent each digit
• one G character, which represents a locale-aware group separator

The important thing to note is that it doesn’t use the D format element, which is the format aware decimal character. Nor does it use any literal decimal characters (such as a comma or period). And because it doesn’t have any decimal characters, there are obviously no digits following such a character.

If we did have a decimal character, any 9 or 0 elements to the right of it could result in decimal places being included in the result.

Example:

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

Result:

7.00

To suppress the trailing zeros, we can change the 0 characters to 9s. However, removing the fm modifier can result in trailing zeros appearing even when using the 9 format element.

Here’s a comparison to illustrate what I mean:

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

Result:

     r1    r2        r3 
_______ _____ _________ 
7.00    7.      7.00   

The ROUND() Function

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

SELECT ROUND(1234.56, 0)
FROM DUAL;

Result:

1235

The ROUND() function rounds a number to a given decimal place. Here, we specified zero decimal places, which removes all decimal places from the number.

In this case, the number was rounded up.

The TRUNC() Function

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

SELECT TRUNC(1234.56, 0)
FROM DUAL;

Result:

1234

The TRUNC() function truncates a number to a given decimal place. No rounding occurs. Therefore, this number was truncated to zero decimal places, with no rounding.

The CAST() Function

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

SELECT CAST(1234.56 AS INT) 
FROM DUAL;

Result:

1235

The CAST() function converts its argument to a different data type. In this case we converted the number to an integer. Integers have no decimal places, and so all fractional parts are removed from the result (including the decimal character). Note that the result has been rounded up.