Rounding numbers in Oracle SQL

When working with numbers in an Oracle database you will have to round the numbers at some point in time. When you are creating SQL code it is good to know that you have primarily 3 options for rounding numbers in Oracle SQL. The 3 main options you have at your use are round, floor and ceil. All have there own use and should be used in the correct way in your code.

The ceil option wil give you the smallest integer greater or equal to the given number. Meaning; the ceil option will round the number upwards to a whole number. for example 1.4 will become 2 however 1.5 or 1.6 will also become 2.

select ceil(1.4) from dual;

CEIL(1.4)
---------
        2 


select ceil(1.5) from dual;

CEIL(1.5)
---------
        2 

The floor option will do exactly the opposite and will round down in the same way as ceil is doing downwards. Below you can see a rounding example using floor.

select floor(1.4) from dual;

FLOOR(1.4)
----------
         1 


select floor(1.5) from dual;

FLOOR(1.5)
----------
         1 

Both floor and ceil are very convenient options when you have to round a number to a integer however in many cases rounding to an integer is not what you would like. For "true" rounding you can make use of the SQL round function in an Oracle database.

When no additional information is given the round function will round a number to a integer like result. For example if we round 1.4432123421 it provide a return set of 1.

select round(1.4432123421) from dual;

ROUND(1.4432123421)
-------------------
                  1 

However, giving some options will make sense in most cases, below are some examples of rounding;

select round(1.4432123421,1) from dual;

ROUND(1.4432123421,1)
---------------------
                  1.4 


select round(1.4432123421,2) from dual;

ROUND(1.4432123421,2)
---------------------
                 1.44


select round(1.4432123421,3) from dual;


ROUND(1.4432123421,3)
---------------------
                1.443