How To Use CEIL In Oracle SQL

How To Use CEIL In Oracle SQL

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This tutorial is based on examples so it would be easier to understand. Oracle Ceil function allows to round a number up and the Ceil function removes all numbers after the decimal place and returns an integer. The syntax of Oracle Ceil is:

CEIL (<number>);

The following Oracle Ceil example has input value “11.11111” and it will round up the number to “12“. The Ceil function always removes all numbers after the decimal place.

 SELECT CEIL (11.11111)
   FROM dual;

dba oracle dba system database management system oracle performance tuning oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql learn sql financial advisor personal financial

When the values on left of the decimal point are all zeros then Oracle Ceil returns equal integer value as the same number being integer. The next example turns “11.00” to “11“.

 SELECT CEIL (11.00)
   FROM dual;

dba oracle dba system database management system oracle performance tuning oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql learn sql financial advisor personal financial

The Ceil function will round up even the smallest fraction of the number and in this case the input number is “11.00000001” and no any other rounding function beside Ceil will return number “12” from it.

 SELECT CEIL (11.00000001)
   FROM dual;

dba oracle dba system database management system oracle performance tuning oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql learn sql financial advisor personal financial

The third example’s value “11.9999999” will become “12” even with the Oracle Round function and the example demonstrates that there is no exceptions in Oracle Ceil.

 SELECT CEIL (11.9999999)
   FROM dual;

dba oracle dba system database management system oracle performance tuning oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql learn sql financial advisor personal financial

This example is about how to round up using Oracle Ceil with keeping the decimal places in number. First we do know Ceil returns only an integer value, so we need to move decimal place to a lower position before applying function Ceil. The following example we would like to keep 3 numbers after a decimal point and we will multiply the input number “11.11111” with “1000“.

 SELECT 11.11111 * 1000
   FROM dual;

dba oracle dba system database management system oracle performance tuning oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql learn sql financial advisor personal financial

Now the number has become “11111.11” and it is time to apply Oracle Ceil on it.

 SELECT CEIL (11111.11)
   FROM dual;

dba oracle dba system database management system oracle performance tuning oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql learn sql financial advisor personal financial

To get the numbers after decimal point back we need to divide the last integer (11112) with “1000“.

 SELECT 11112 / 1000
   FROM dual;

dba oracle dba system database management system oracle performance tuning oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql learn sql financial advisor personal financial

The example above is a workaround to do round up and to keep the decimal places. The output on the last example cannot be achieved using Oracle Round because as it does not round up. Take a look at the following example.

 SELECT ROUND(11.11111,3)
  FROM dual;

dba oracle dba system database management system oracle performance tuning oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql learn sql financial advisor personal financial

As you see on the output it didn’t become 11.112 instead it is 11.111.

To round up or down numbers take a look at the links below. To round down and to receive an integer value use Oracle Floor or Oracle Trunc and to normal rounding use Oracle Round function.

See Also:
Oracle Select Oracle Round Oracle Trunc Home

Leave a Reply

Your email address will not be published. Required fields are marked *

*