Oracle Functions

ORACLE FUNCTIONS

·         SIGN(n)  :     positive = 1, zero = 0, negative = -1 

·         SIN(n)    :    Sine of n in Radians 

·         SINH(n)  :     Hyperbolic Sine of n in Radians

·         SOUNDEX(char)  : It returns a char value representing the sound of the words 

·         SQRT(n)   :   to find  Square Root of a number

                                  Eg : sqrt(4)=2 

·         STDDEV([DISTINCT] n) :  Standard deviation of n 

·         SUBSTR(char, s[,l]) : A substring of char, starting at character s, length l 

·         SUBSTRB(char, s[,l]) : A substring of char, starting at character s, length l. The same as SUBSTR, except that 's', 'l' and the return value are expressed in bytes, use for double-byte char sets 

·         SUM([DISTINCT] n) :  Sum of values of n, ignoring NULLs 

·         SYS_CONTEXT('namespace','attribute_name') :  Examine the package associated with the context namespace .

              Possible attributes are: NLS_TERRITORY, NLS_CURRENCY, NLS_CALENDAR ,              NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, SESSION_USER, CURRENT_USER, CURRENT SCHEMAID,SESSION_USERID, CURRENT_USERID 

·         SYS_CONTEXT ('USERENV','IP_ADDRESS') 

·         SYS_GUID()  :  Returns a globally unique identifier (16 byte RAW value) 

·         SYSDATE   :    The current system date & time

·         TAN(n)      :  Tangent of n in Radians

·         TANH(n)     :  Hyperbolic tangent of n in Radians

·         TO_CHAR  :     Convert to character String

                               Eg :to_char(to_date(‘1/sep/2009’),’MON’)  =sep 

·         TO_DATE  :     Convert to date value 

·         TO_LOB(long)  : Convert LONG values to CLOB or NCLOB values  or convert LONG RAW values to BLOB values.  Use only as part of an "INSERT INTO ... SELECT..." subquery.  

·         TO_MULTI_BYTE(char) :  Convert single-byte char to multi-byte char. 

·         TO_NUMBER   :  Convert to numeric format

·         TO_SINGLE_BYTE(char) :  Convert multi-byte char to single-byte char.

·         TRANSLATE('char','search_str','replace_str') :Replace every occurrence of search_str with replace_str  unlike REPLACE() if replace_str is NULL the function returns NULL 

·         TRANSLATE (text USING charset) :  Convert text into a specific character set. Use this instead of CONVERT() if either the input or output datatype  is NCHAR or NVARCHAR2. 

·         TRIM(LEADING|TRAILING|BOTH trim_char FROM trim_source) : Returns trim_source as a VARCHAR2 with leading/trailing items removed.  trim_char defaults to a space ' ' but may be numeric or char 'A' 

·         TRUNC(i,d)  :  i truncated to d decimal places (d defaults to 0)

·         TRUNC(date,fmt) :  date truncated to nearest fmt

·         UID  :         User id - unique number

·         UPPER(char)  :  Returns Chars in uppercase 

·         USER      :    Returns the current Username 

·         USERENV('option') : Can return any of the options: ENTRYID, SESSIONID, TERMINAL, LANGUAGE, ISDBA, LANG, INSTANCE, CLIENT_INFO 

·         VALUE(correlation_variable) : Return the object instance for a row of an object table as associated with the correlation_variable 

·         VARIANCE([DISTINCT] n) : Variance of n, ignoring NULLs

·         VSIZE(expr)  :  Value Size - returns the number of bytes used by each row of expr.

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]

Oracle Functions

Oracle SQL Functions

·         ABS(n)  : It returns absolute value of a number

          Eg:          ABS(-15)=15, ABS(24)=24

·         ACOS(n) : It Returns arc cosine of number(n).’n’ must be in the   range of -1 and 1

                                              Eg:          ACOS(.3)= 1.26610367

·         ADD_MONTHS(date,num_months) : Returns date + num_months

                                  Eg: ADD_MONTHS('01-Aug-03', 3)= '01-Nov-03'

·         ASCII(char) : it Converts char into a decimal ascii code

                                  Eg:          ASCII(3)=51

·         ASCIISTR(char):argument of this function is a string ,or expression that resolves to a string ,in any character set.it returns an ASCII conversions of the string in the database character set.

                                  Eg:          ASCIISTR(ABCDE)= AB\00C4CDE

·         ASIN(n) : returns arc sine of n.

                                  Eg:          ASIN(.3)= 0.30469265

·         ATAN(n) : it returns arc tangent of n.

                                  Eg:          ATAN(.3)= 0.29145679

·         ATAN2(n.m) : it returns arc tangent of n and m. 

                                  Eg:          ATAN2(.3,.2)= 0.9827937232

·         AVG([DISTINCT]n): Averge value of 'n' ignoring NULLs

·         BETWEEN value AND value : Where 'x' between 25 AND 100

·         BFILENAME('directory','filename'): To Get the BFILE locator associated with a physical LOB binary file.

·         CASE : It is used to Group the data into sub-sets. The case statement is an alternative for the decode function. 

Eg: select id

, case (when status ='A' then 'Accepted'

when status ='R' then 'Rejected'

else 'Other') end

from employee_maser;

·         CEIL(n) :       Round n up to next whole number.

                    Eg: ceil(3.4)=4,        Ceil(3.9)=4

·         CHARTOROWID(char) : it Converts a Char into a rowid value.

                              Eg : SELECT ID from some_table 

                                              WHERE ROWID = CHARTOROWID('AAAAtmAAEAAAAFmAAB');     

·         CHR(n) : Character with value n

                                             Eg: chr(51)=3    

·         CONCAT(s1,s2) :  it Concatenate string1 and string2

                                       Eg : concat('dfq','ee')=dfqee

·         CONVERT (char_to_convert, new_char_set, old_char_set) : 

                               Convert a  string from one character set to another.

·         COS(n): Cosine of number

                  Eg : cos(.3)= 0.95533648

·         COSH(n)       Hyperbolic Cosine of number

                  Eg : cosh(.3)= 1.04533851

·         COUNT(*) :     Count the no of rows returned

·         COUNT([DISTINCT] expr) :Count the no of rows returned by expr

·         DECODE :        IF x THEN return y ELSE return z

                                                             Eg : select decode(id,1,’111’,’2’)

                                                    If id=1 then it return ‘111’ else returns ‘2’

·         DENSE_RANK  :  It is used to calculate the rank of a value in a group

·         DEREF(e)   :   Return the object reference of argument e. 

·         DUMP(expr,fmt[,start,length]) :   Convert to dec/hex/oct and display char set

·         EMPTY_BLOB  :   Return an empty LOB locator

·         EMPTY_CLOB  :  Return an empty LOB locator 

·         EXISTS  :     This function returns  TRUE if a subquery returns at least one row

·         EXP(n)      :  Exponential (e to 'n'th power)

·         FLOOR(n)    :   Round n down to the next whole number.  

                                              Eg : Floor(3.6)=4

·         GREATEST(expression, expression...) :  It returns the largest in a list of expressions.

·         GROUPING   :   Grouping for superaggregate rows=NULL

·         HEXTORAW(char) :  Convert char containing hex digits to a raw value.

·         IN (list of comma separated values) : it is used to check whether the parametercontains a required value.       

                                  Eg : emp_code in(10002,35254,43534) –return TRUE if list contains the                   requied emp_code

·         INITCAP(char) :  String with Initial Capitals

                   Eg : Initcap(‘lima’)=Lima

·         INSTR(str, chars[,s[,n]]) :  this function find the 'n'th occurence of 'chars' in 'str'  Starting at position 's'.  n and s default to 1

·         INSTRB (str, chars[,s[,n]]) :  It is same as INSTR, except that 's' and the return value are expressed in bytes,   use for double-byte char sets

·         IS [NOT] NULL  : It is used to check for NULL (empty) values  Where 'x' IS NULL;