How To Use FLOOR In Oracle SQL

How To Use FLOOR 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 help is based on examples so it would be easier to understand. Oracle Floor function allows to round a number down and the Floor function removes all numbers after the decimal point. The syntax of Oracle Floor is:

FLOOR (<number>);

The following Oracle Floor example has input value “11.9999999” and function Oracle Round would have returned number “12″ but since Oracle Floor rounds the number down it returns an integer value number “11“. The function always removes all numbers after the decimal point.

 SELECT FLOOR (11.9999999)
   FROM dual;

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

The second example shows that there is no difference in the output value from the example above even when the input number is “11.11111“. Oracle Floor does round down and it returns “11” again.

 SELECT FLOOR (11.11111)
   FROM dual;

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

The third Oracle Floor example applies on number “11.00” and it returns an integer “11” once again. The function job is this time to remove the nils.

 SELECT FLOOR (11.00)
   FROM dual;

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

To demonstrate how to round down and to leave some numbers after the decimal point 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 SQL example below the output is “11.988” and this is not rounded down. The “11.987…” has become “11.988“, so the number achieved by Oracle Round does not fill our needs. That does mean it didn’t round the number down.

 SELECT ROUND (11.9876543,3)
   FROM dual;

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

When we are going to use Oracle Floor function it removes all numbers after the decimal point and to keep them we need to move the decimal point 3 places down. To do so we will multiply the “11.9876543” value with “1000“. Take a look at the output. The number “11.9876543” has become “11987.6543” and we are ready to use Oracle Floor on it.

 SELECT 11.9876543 * 1000
   FROM dual;

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

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;

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

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

 SELECT 11987 / 1000
   FROM dual;

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

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;

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

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

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

Leave a Reply

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

*