Oracle Rounding Functions: ROUND, TRUNC & CEIL

1 year ago
Liam
2 minutes

Oracle offers several functions for rounding numbers, as outlined below:

  1. ROUND function: This function rounds a number to the specified number of decimal places. The syntax is as follows:
    ROUND(n, d)
    Where n is the number to be rounded and d is the number of decimal places to retain.
  2. The TRUNC function truncates a number to a specific number of decimal places. Syntax: TRUNC(n, d) where n is the number to be truncated and d is the number of decimal places to retain.
  3. CEIL function: This function rounds a number up. The syntax is as follows:
    CEIL(n)
    Where n is the number to be rounded.
  4. FLOOR function: This function rounds a number down to the nearest whole number. The syntax is as follows:
    FLOOR(n)
    Where n is the number to be rounded down.

For example, to round a number to two decimal places, you can use the ROUND function:
SELECT ROUND(3.14159, 2) FROM DUAL;
The result is 3.14.

To round a number to two decimal places, you can use the TRUNC function:
SELECT TRUNC(3.14159, 2) FROM DUAL;
The result is 3.14.

To round up a numerical value, you can use the CEIL function:
SELECT CEIL(3.14159) FROM DUAL;
The result is 4.

To round a number down, you can use the FLOOR function:
SELECT FLOOR(3.14159) FROM DUAL;
The result is 3.

bannerAds