Infolinks

Saturday, 12 May 2012

SQL-FUNCTIONS1


FUNCTIONS

Functions can be categorized as follows.


Ø  Single row functions
Ø  Group functions

SINGLE ROW FUNCTIONS

Single row functions can be categorized into five. These will be applied for each row and produces individual output for each row.

Ø  Numeric functions
Ø  String functions
Ø  Date functions
Ø  Miscellaneous functions
Ø  Conversion 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

No comments:

Post a Comment