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;