How To Use Oracle ROUND Function
  Home
 

This tutorial is based on examples to be easier to follow. The Oracle Round function allows rounding a number value to “x” places up after or before the decimal point. The Oracle Round syntax is following:

ROUND (<number> [,<rounding_integer>]);

This Oracle Round example has input value 12.3333333 and it will round the number up to a decimal place and it returns an integer value (number 12). This way rounding is done when the “rounding integer” has left out and by default the Round function removes all numbers after decimal place.

 SELECT ROUND (12.3333333) AS rounded
   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 database programming database oracle retirement planning retirement sql database oracle database sql

To leave some numbers after the decimal place use a positive number in the “rounding integer” place. The following example has the same input number as on the last example and the rounding integer is set 2. The output shows the 12.33 value that is exactly two numbers after the decimal place.

 SELECT ROUND (12.3333333,2) AS rounded
   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 programming database oracle retirement planning retirement sql database oracle database sql

To round up before the decimal place use a negative number in the “rounding integer” place. This example we will round only 1 place up of value 12.3333333 and the output value has become number 10. If the input values would have been 16 then the output would have number 20 now.

 SELECT ROUND (12.3333333,-1) AS rounded
   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 programming database oracle retirement planning retirement sql database oracle database sql

The following example has almost the same query as the last one – only this time we will round up 2 places before decimal place. The “rounding integer” has set “-2” and the number 12.3333333 has become 0.

 SELECT ROUND (12.3333333,-2) AS rounded
   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 programming database oracle retirement planning retirement sql database oracle database sql

The SQL query below does rounding with a dynamic value and it comes from pseudo-column named “Rownum“. Oracle ROWNUM value grows with every next line and that since it has been used as “rounding integer” than leaves more decimal places to available with every next line until it becomes a full value.

 SELECT ROUND (12.3333333,rownum) AS rounded
   FROM dual
    CONNECT BY rownum < 8;

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 programming database oracle retirement planning retirement sql database oracle database sql

The output shows how the rounding leaves more number available after the decimal place with growing Oracle ROWNUM value.



See Also:
Home 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)

*