NUMERIC FUNCTIONS IN ORACLE WITH EXAMPLES

These are functions that accept numeric input and return numeric values. Below are few of the examples

• ABS: Absolute value of the number

SELECT ABS(12) FROM DUAL;

  ABS(12)

---------

       12

• CEIL: Integer value that is Greater than or equal to the number

SQL> SELECT CEIL(48.99) FROM DUAL;

CEIL(48.99)

-----------

         49

SQL> SELECT CEIL(48.11) FROM DUAL;

CEIL(48.11)

-----------

         49

• FLOOR: Integer value that is Less than or equal to the number

SQL> SELECT FLOOR(49.99) FROM DUAL;

FLOOR(49.99)

------------

          49

SQL> SELECT FLOOR(49.11) FROM DUAL;

FLOOR(49.11)

------------

          49

• ROUND: Rounded off value of the number 'x' up to the number 'y' decimal places

SQL> SELECT ROUND(49.11321,2) FROM DUAL;

ROUND(49.11321,2)

-----------------

            49.11

SQL> SELECT ROUND(49.11321,3) FROM DUAL;

ROUND(49.11321,3)

-----------------

           49.113

SQL> SELECT ROUND(49.11321,4) FROM DUAL;

ROUND(49.11321,4)

-----------------

          49.1132

Few other functions,

• POWER

SQL> SELECT POWER(4,2) FROM DUAL;

POWER(4,2)

----------

        16

• MOD

SQL> SELECT MOD(4,2) FROM DUAL;

 MOD(4,2)

---------

        0

SQL> SELECT SIGN(-98) FROM DUAL;

SIGN(-98)

---------

       -1

SQL> SELECT SIGN(98) FROM DUAL;

 SIGN(98)

---------

        1