How To Use Oracle Floor Function
  Home
 

This online tutorial page is based on examples. The Oracle Floor function allows to round a number value down and the function removes all numbers after the decimal place. The Oracle Floor syntax is following:

FLOOR (<number>);

The first Oracle Floor example has input value 11.9999999 and the Oracle Round function would return number 12 but since Oracle Floor rounds the number value down it returns an integer value number 11. The Floor function always removes all numbers after a decimal place.

 SELECT FLOOR (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 floor oracle floor in oracle sql floor oracle sql floor in oracle sql

The second example shows there is no difference in the output value even when the input number is in this time 11.11111.

 SELECT FLOOR (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 floor oracle floor in oracle sql floor oracle sql floor in oracle sql

The third Floor example applies on number 11.00 and it returns an integer 11 once again.

 SELECT FLOOR (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 floor oracle floor in oracle sql floor oracle sql floor in oracle sql

To demonstrate how to round down and to leave some numbers after the decimal place we are going to use number 11.9876543 and we will keep 3 numbers after the decimal place. First lets take a look why Oracle Round does not suit here. Take a look at the example below the SQL output value is 11.988 and this is not rounded down, so the value does not fill our needs.

 SELECT ROUND (11.9876543,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 floor oracle floor in oracle sql floor oracle sql floor in oracle sql

When we are going to use Oracle Floor function it will remove all decimal places and to keep them we need to move the decimal place 3 places down. To do so we will multiply the 11.9876543 value with 1000. Take a look at the output number 11.9876543 became 11987.6543 and we are ready to apply Oracle Floor on it.

 SELECT 11.9876543 * 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 floor oracle floor in oracle sql floor oracle sql floor in oracle sql

As we mentioned above we will use Oracle Floor function on the 11987.6543 number and after removing the decimal places the value becomes 11987.

 SELECT FLOOR(11987.6543)
   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 floor oracle floor in oracle sql floor oracle sql floor in oracle sql

To move the number on its original look we need to divide number 11987 with 1000 and the result value is 11.987 which also is rounded down.

 SELECT 11987 / 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 floor oracle floor in oracle sql floor oracle sql floor in oracle sql

Now all this hassle above can be skip if you’ll use function Oracle Trunc. This function truncates (read rounds down) the input value depending on the second parameter. Take a look at the output it is same 11.987 as at the example above.

 SELECT TRUNC (11.9876543,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 floor oracle floor in oracle sql floor oracle sql floor in oracle sql

To read more about Oracle Trunc, Round or Ceil take a look at the links below.


See Also:
Oracle Ceil Oracle Round Oracle Select Oracle To_Number Oracle Trunc

 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)

*