Friday, October 28, 2011

ORACLE SQL TUTORIAL : NUMERIC FUNCTIONS


NUMERIC FUNCTIONS
Ø  Abs
Ø  Sign
Ø  Sqrt
Ø  Mod
Ø  Nvl
Ø  Power
Ø  Exp
Ø  Ln
Ø  Log
Ø  Ceil
Ø  Floor
Ø  Round
Ø  Trunk
Ø  Bitand
Ø  Greatest
Ø  Least
Ø  Coalesce
a) ABS

     Absolute value is the measure of the magnitude of value.
     Absolute value is always a positive number.

     Syntax: abs (value)

     Ex:
          SQL> select abs(5), abs(-5), abs(0), abs(null) from dual;

                     ABS(5)    ABS(-5)     ABS(0)  ABS(NULL)
                     ---------- ----------    ---------- -------------
                          5              -5                0

b) SIGN

     Sign gives the sign of a value.

     Syntax: sign (value)

     Ex:
          SQL> select sign(5), sign(-5), sign(0), sign(null) from dual;

                                SIGN(5)   SIGN(-5)    SIGN(0) SIGN(NULL)
----------   ----------     ---------- --------------
       1            -1                       0

c) SQRT

     This will give the square root of the given value.

     Syntax: sqrt (value)    --  here value must be positive.

     Ex:
          SQL> select sqrt(4), sqrt(0), sqrt(null), sqrt(1) from dual;

                                SQRT(4)    SQRT(0) SQRT(NULL)    SQRT(1)
----------    ---------- ---------------    ----------
         2               0                                                   1

d) MOD

     This will give the remainder.

     Syntax: mod (value, divisor)  

     Ex:
          SQL> select mod(7,4), mod(1,5), mod(null,null), mod(0,0), mod(-7,4) from dual;

                                MOD(7,4)   MOD(1,5) MOD(NULL,NULL)   MOD(0,0)  MOD(-7,4)
------------   ----------  ---------------------    ----------- -------------
         3               1                                                   0              -3

e) NVL

     This will substitutes the specified value in the place of null values.

     Syntax: nvl (null_col, replacement_value)       

     Ex:
          SQL> select * from student;              -- here for 3rd row marks value is null

                                 NO NAME      MARKS
 --- -------      ---------
                                  1            a         100
                                  2            b          200
                                  3            c

SQL> select no, name, nvl(marks,300) from student;


                                NO NAME  NVL(MARKS,300)
---  -------  ---------------------
           1   a             100
                           2   b             200
           3   c             300

SQL> select nvl(1,2), nvl(2,3), nvl(4,3), nvl(5,4) from dual;

  NVL(1,2)   NVL(2,3)   NVL(4,3)   NVL(5,4)
  ----------    ----------    ----------    ----------
         1               2                   4                   5

SQL> select nvl(0,0), nvl(1,1), nvl(null,null), nvl(4,4) from dual;
 
  NVL(0,0)   NVL(1,1) NVL(null,null)  NVL(4,4)
  ----------    ---------- -----------------   ----------
         0              1                                                         4

f) POWER

     Power is the ability to raise a value to a given exponent.

     Syntax: power (value, exponent)        

     Ex:
          SQL> select power(2,5), power(0,0), power(1,1), power(null,null), power(2,-5) from 
                  dual;
 
POWER(2,5) POWER(0,0) POWER(1,1) POWER(NULL,NULL) POWER(2,-5)
--------------  --------------  ----- --------- -----------------------  ---------------
        32                    1                  1                                                         .03125

g) EXP

     This will raise e value to the give power.

     Syntax: exp (value)   

     Ex:
          SQL> select exp(1), exp(2), exp(0), exp(null), exp(-2) from dual;

                                EXP(1)                   EXP(2)                   EXP(0)  EXP(NULL)    EXP(-2)
--------                   ---------                 --------  -------------    ----------
2.71828183  7.3890561          1                          .135335283

h) LN

     This is based on natural or base e logarithm.

     Syntax: ln (value)        -- here value must be greater than zero which is positive only.

     Ex:
          SQL> select ln(1), ln(2), ln(null) from dual;

                                LN(1)      LN(2)      LN(NULL)
-------      -------      ------------
                                    0            .693147181

          Ln and Exp are reciprocal to each other.
            EXP (3) = 20.0855369
            LN (20.0855369) = 3

i) LOG

    This is based on 10 based logarithm.

    Syntax: log (10, value)                -- here value must be greater than zero which is positive only.  

    Ex:
          SQL> select log(10,100), log(10,2), log(10,1), log(10,null) from dual;

LOG(10,100)  LOG(10,2)  LOG(10,1) LOG(10,NULL)
---------------   -----------   ------------  -----------------
                             2            .301029996          0

LN (value) = LOG (EXP(1), value)

SQL> select  ln(3), log(exp(1),3) from dual;

                                LN(3)      LOG(EXP(1),3)
-------      -----------------
1.09861229    1.09861229

j) CEIL

     This will produce a whole number that is greater than or equal to the specified value.

     Syntax: ceil (value)    

     Ex:
          SQL> select ceil(5), ceil(5.1), ceil(-5), ceil( -5.1), ceil(0), ceil(null) from dual;

                                CEIL(5)  CEIL(5.1)   CEIL(-5) CEIL(-5.1)    CEIL(0) CEIL(NULL)
---------  -----------    ---------- ------------     --------  --------------
                                        5            6                -5            -5                 0

k) FLOOR

     This will produce a whole number that is less than or equal to the specified value.

     Syntax: floor (value) 

     Ex:
          SQL> select floor(5), floor(5.1), floor(-5), floor( -5.1), floor(0), floor(null) from dual;

                                FLOOR(5) FLOOR(5.1)  FLOOR(-5) FLOOR(-5.1)   FLOOR(0) FLOOR(NULL)
-----------  -------------   ------------  --------------    -----------  ----------------
         5               5                 -5                 -6                   0

l) ROUND

    This will rounds numbers to a given number of digits of precision.

     Syntax: round (value, precision)          

     Ex:
          SQL> select round(123.2345), round(123.2345,2), round(123.2354,2) from dual;

        ROUND(123.2345)  ROUND(123.2345,0) ROUND(123.2345,2) ROUND(123.2354,2)
                   ---------------------   ------------------------  -----------------------  -----------------------
                                123                          123                            123.23                      123.24

            SQL> select round(123.2345,-1), round(123.2345,-2), round(123.2345,-3),
                  round(123.2345,-4) from dual;
   
ROUND(123.2345,-1) ROUND(123.2345,-2) ROUND(123.2345,-3) ROUND(123.2345,-4)
           ------------------------  -------------------------  ------------------------   ------------------------
                           120                                100                            0                                         0

SQL> select round(123,0), round(123,1), round(123,2) from dual;

ROUND(123,0) ROUND(123,1) ROUND(123,2)
-----------------  -----------------  ----------------
         123                   123                                      123

SQL> select round(-123,0), round(-123,1), round(-123,2) from dual;

ROUND(-123,0) ROUND(-123,1) ROUND(-123,2)
------------------  -----------------   -------------------
                                -123                 -123                    -123

SQL> select round(123,-1), round(123,-2), round(123,-3), round(-123,-1), round(-123,- 
        2), round(-123,-3) from dual;

ROUND(123,-1) ROUND(123,-2) ROUND(123,-3) ROUND(-123,-1) ROUND(-123,-2)          
ROUND(-123,-3)
           ------------- ------------- ------------- -------------- -------------- --------------
                     120           100             0               -120               -100              0

SQL> select round(null,null), round(0,0), round(1,1), round(-1,-1), round(-2,-2) from
          dual;

ROUND(NULL,NULL) ROUND(0,0) ROUND(1,1) ROUND(-1,-1) ROUND(-2,-2)
-----------------------  --------------  --------------  ----------------  ----------------
                                                                                0                       1                   0                    0
     
m) TRUNC

      This will truncates or chops off digits of precision from a number.

      Syntax: trunc (value, precision)           

      Ex:
          SQL> select trunc(123.2345), trunc(123.2345,2), trunc(123.2354,2) from dual;

TRUNC(123.2345) TRUNC(123.2345,2) TRUNC(123.2354,2)
---------------------  -----------------------  -----------------------
            123                        123.23                     123.23

SQL> select trunc(123.2345,-1), trunc(123.2345,-2), trunc(123.2345,-3),
         trunc(123.2345,-4) from dual;

TRUNC(123.2345,-1) TRUNC(123.2345,-2) TRUNC(123.2345,-3) TRUNC(123.2345,-4)
------------------------  ------------------------   -----------------------  ------------------------
               120                              100                                0                             0

SQL> select trunc(123,0), trunc(123,1), trunc(123,2) from dual;

TRUNC(123,0) TRUNC(123,1) TRUNC(123,2)
----------------   ----------------  -----------------
         123                    123                 123

SQL> select trunc(-123,0), trunc(-123,1), trunc(-123,2) from dual;

TRUNC(-123,0) TRUNC(-123,1) TRUNC(-123,2)
-----------------   -----------------  -----------------
         -123                    -123                -123

SQL> select trunc(123,-1), trunc(123,-2), trunc(123,-3), trunc(-123,-1), trunc(-123,2),
         trunc(-123,-3) from dual;

TRUNC(123,-1) TRUNC(123,-2) TRUNC(123,-3) TRUNC(-123,-1) TRUNC(-123,2) TRUNC(-
123,-3)
------------- ------------- ------------- -------------- ------------- --------------
          120           100             0                   -120          -123              0

SQL> select trunc(null,null), trunc(0,0), trunc(1,1), trunc(-1,-1), trunc(-2,-2) from dual;

TRUNC(NULL,NULL) TRUNC(0,0) TRUNC(1,1) TRUNC(-1,-1) TRUNC(-2,-2)
-----------------------  -------------  -------------  ---------------  ----------------
                                                               0                   1                     0                      0

n) BITAND

     This will perform bitwise and operation.

     Syntax: bitand (value1, value2)            

     Ex:
          SQL> select bitand(2,3), bitand(0,0), bitand(1,1), bitand(null,null), bitand(-2,-3) from
                   dual;
BITAND(2,3) BITAND(0,0) BITAND(1,1) BITAND(NULL,NULL) BITAND(-2,-3)
--------------  ---------------  --------------   ------------------------  -----------------
          2                    0              1                                                                              -4

o) GREATEST

     This will give the greatest number.

     Syntax: greatest (value1, value2, value3 … valuen)     

     Ex:
          SQL> select greatest(1, 2, 3), greatest(-1, -2, -3) from dual;

GREATEST(1,2,3) GREATEST(-1,-2,-3)
--------------------  -----------------------
              3                                -1

Ø  If all the values are zeros then it will display zero.
Ø  If all the parameters are nulls then it will display nothing.
Ø  If any of the parameters is null it will display nothing.

p) LEAST

    This will give the least number.

    Syntax: least (value1, value2, value3 … valuen)             

     Ex:
          SQL> select least(1, 2, 3), least(-1, -2, -3) from dual;

LEAST(1,2,3)         LEAST(-1,-2,-3)
--------------------  -----------------------
              1                                -3
Ø  If all the values are zeros then it will display zero.
Ø  If all the parameters are nulls then it will display nothing.
Ø  If any of the parameters is null it will display nothing.
q) COALESCE

    This will return first non-null value.

    Syntax: coalesce (value1, value2, value3 … valuen)      

     Ex:
            SQL> select coalesce(1,2,3), coalesce(null,2,null,5) from dual;

COALESCE(1,2,3) COALESCE(NULL,2,NULL,5)
-------------------   -------------------------------
              1                                                2




0 Responses to “ORACLE SQL TUTORIAL : NUMERIC FUNCTIONS”

Post a Comment

Subscribe to: Post Comments (Atom)

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.


All Rights Reserved Oracle Apps Knowledge Sharing | Home by Team Search