Monday, 1 August 2016

Conversion and Miscellaneous Functions

MISCELLANEOUS FUNCTIONS

a)    Uid
b)    User
c)    Vsize
d)    Rank
e)    Dense_rank

a) UID

     This will returns the integer value corresponding to the user currently logged in.

     Ex: SQL> select uid from dual;

       UID
    ----------
       319

b) USER

     This will returns the login’s user name.

     Ex:  SQL> select user from dual;


USER
--------
RAM

c) VSIZE

     This will returns the number of bytes in the expression.

     Ex:    SQL> select vsize(123), vsize('computer'), vsize('12-jan-90') from dual;

VSIZE(123)         VSIZE('COMPUTER')        VSIZE('12-JAN-90')
-------------  -----------------------  ----------------------
         3                     8                          9

d) RANK

     This will give the non-sequential ranking.

     Ex:    SQL> select rownum,sal from (select sal from emp order by sal desc);
    ROWNUM        SAL
    ---------- ----------
         1          5000
         2          3000
         3          3000
         4          2975
         5          2850
         6          2450
         7          1600
         8          1500
         9          1300
        10          1250
        11          1250


     SQL> select rank(2975) within group(order by sal desc) from emp;

RANK(2975)WITHINGROUP(ORDERBYSALDESC)
---------------------------------------------------------
                                    4

d) DENSE_RANK

     This will give the sequential ranking.
    
Ex:    SQL> select dense_rank(2975) within group(order by sal desc) from emp;

DENSE_RANK(2975)WITHINGROUP(ORDERBYSALDESC)
-----------------------------------------------------------------
                                          3

CONVERSION FUNCTIONS

a)    Bin_to_num
b)    Chartorowid
c)    Rowidtochar
d)    To_number
e)    To_char
f)    To_date

a) BIN_TO_NUM

     This will convert the binary value to its numerical equivalent.

     Syntax: bin_to_num( binary_bits)

     Ex:    SQL> select bin_to_num(1,1,0) from dual;

BIN_TO_NUM(1,1,0)
------------------------
                6

1)    If all the bits are zero then it produces zero.
2)    If all the bits are null then it produces an error.

b) CHARTOROWID

     This will convert a character string to act like an internal oracle row identifier or rowid.

c) ROWIDTOCHAR

    This will convert an internal oracle row identifier or rowid to character string.

d) TO_NUMBER

    This will convert a char or varchar2 to number.

e) TO_CHAR

    This will convert a number or date to character string.

f) TO_DATE

    This will convert a number, char or varchar2 to a date.

No comments:

Post a Comment