The personal view on the IT world of Johan Louwers, specially focusing on Oracle technology, Linux and UNIX technology, programming languages and all kinds of nice and cool things happening in the IT world.

Sunday, November 25, 2012

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.

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.