Oracle Functions

ORACLE FUNCTIONS
·         LAST_DAY(date) : Returns the last day of month in Date
                   Eg : last_day('23/sep/2009')  = 9/30/2009
 ·         LEAST(expression, expression...) :  Returns the smallest in a list of expressions
                   Eg : least (2,4,3)=2
 ·         LENGTH(char)  :  Returns the number of characters in char
                   Eg : length(‘abc’)=3
 ·         LENGTHB(char) :  Returns the number of bytes in char                
·         LIKE wildcard/value :    Wildcards are [% = any chars] [ _ = any one char]
                                                   Where 'x' LIKE 'ajith%' [will find 'Ajithraj']
                                                   Where 'x' LIKE 'ajith_' [will find 'Ajitha']
·         LN(n)      :    Natural Log of n, where n>0
·         LOG(b,n)    :  log of n, base b
            ·         LOWER(char)  :  Returns character string in lowercase
                                              Eg : lower(‘Lima’) = lima
·         LPAD(char, n[,PadChar]) :    Left Pad char with n spaces [or PadChars]
                                  Eg : lpad('abc',5,0) = 00abc
·         LTRIM(char[,set]) : Left Trim char - remove leading spaces [or char set]
·         MAKE_REF(table,key) :  Create a REF to a row of an OBJECT view/table
·         MAX([DISTINCT] expr) :  Maximum value returned by expr
·         MIN([DISTINCT] expr) :   Minimum value returned by expr
·         MOD(x,y)  :    Remainder of x divided by y
·         MONTHS_BETWEEN(end_date, start_date) : Number of months between the 2 dates (integer)
·         NEW_TIME(date, zone1, zone2) :   Convert between GMT and US time zones 
·         NEXT_DAY(date,day_of_week) :   '12-OCT-01','Monday' will return the next Mon after 12 Oct
·         NLS_CHARSET_DECL_LEN (bytecount,charset) :  Returns the declaration width (no of chars) of an NCHAR column
·         NLS_CHARSET_ID(varchars) :  Returns the char set ID given a charset name 
·         NLS_CHARSET_NAME(charset_id) :  Returns the char set name given a charset id
·         NLS_INITCAP(char[,'NLS_SORT = sort_sequence']) :  Returns char in Initial Caps, using an NLS sort_sequence  either the session default or specified directly
·         NLS_LOWER(char[,'NLS_SORT = sort_sequence']) : Returns char in lower case, using an NLS sort_sequence  either the session default or specified directly
·         NLSSORT(char[,'NLS_SORT = sort_sequence']) :  Return the string of bytes used to sort char, using an NLS sort_sequence  either the session default or specified directly 
·         NLS_UPPER(char[,'NLS_SORT = sort_sequence']) :  Returns char in UPPER case, using an NLS sort_sequence  either the session default or specified directly
·         NVL(expression, value_if_null) : If expression in the function is null, returns value_if_null; if expression is not null, returns expression.  
·         PERCENT_RANK : Calculate the percent rank of a value in a group.
·         POWER(m,n)  :  m raised to the nth power
·         RANK   :       Calculate the rank of a value in a group
·         RAWTOHEX(raw) : Convert  a raw to a character value containing its hex equivalent
·         REF(table_alias) : Returns a REF value for an object instance .The table alias (correlation variable) is associated with one row of an object table or an object view in an SQL statement. 
·         REFTOHEX(ref)  : Convert ref (object type) to a char value containing its hex equivalent. 
·         REPLACE(char, search_str[, replace_str]) :  ANSI alternative to decode() Replace every occurrence of search_str  with replace_str, replace_str defaults to null.
·         ROUND(n,d)  :  n rounded to d decimal places (d defaults to 0)
                                              Eg : round(3.5345,2)=3.53
·         ROUND(date,fmt) : date rounded to fmt
·         ROWIDTOCHAR(rowid) :  Convert a rowid value to VARCHAR2
·         ROW_NUMBER   : It assign a unique number to each row of the results.  
·         RPAD(char, n[,PadChar]) :   Right Pad char with n spaces [or PadChars]
·         RTRIM(char[,set]) :  Right Trim char - remove trailing spaces [or char set]