How To Use Oracle Ceil Function
  Home
 

This tutorial is based on examples to be easier to follow. The Oracle Ceil function allows rounding a number value up and the function removes all numbers after the decimal place. The Oracle Ceil syntax is following:

CEIL (<number>);

The first Oracle Ceil example has input value 11.11111 and it rounds the number up and returns an integer value (number 12). The Ceil function always removes all numbers after a decimal place.

 SELECT CEIL (11.11111)
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

When the numbers after the decimal place will remain all zeros then Oracle Ceil returns equal integer value and on the following example it is number 11. Have a look at the example below.

 SELECT CEIL (11.00)
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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

 SELECT CEIL (11.00000001)
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The following example will be round to 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;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The next 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 lower position before applying function Ceil. The following example we would like to keep 3 numbers after a decimal place and we will multiply the input number 11.11111 with 1000.

 SELECT 11.11111 * 1000
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

Now it is time to apply Oracle Ceil on 11111.11.

 SELECT CEIL (11111.11)
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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

 SELECT 11112 / 1000
   FROM dual;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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;

online tech support online computer help computer technician computer problems computer oracle round oracle round in oracle sql round oracle sql round in oracle sql database sql oracle ceil oracle ceil in oracle sql ceil oracle sql ceil in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

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

To round up or down a number 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:
Home Oracle Select Oracle To_Number Oracle Trunc Oracle Round

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*