RBN-INFO-TECH : WHAT ARE THE FUNCTIONS IN ORACLE ?

Oracle SQL Functions

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

    Eg:   ABS(-25)=25

          ABS(34)=34

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

                  Eg:   ACOS(.3)= 1.26610367

3.  ADD_MONTHS(date,num_months) : Returns date + num_months

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

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

              Eg:   ASCII(3)=51

5.  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

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

              Eg:   ASIN(.3)= 0.30469265

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

              Eg:   ATAN(.3)= 0.29145679

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

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

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

10.  BETWEEN value AND value : Where 'x' between 25 AND 100

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

12.  CASE : used to Group the data into sub-sets.the case statement is an alternative for the decode function.it is used for the construction of if-then-else.

Eg: select id

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

when status ='D' then 'Denied'

else 'Other') end

from employee_maser;

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

                    Eg: ceil(3.4)=4

                        Ceil(3.9)=4

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

                    Eg : SELECT ID from some_table WHERE ROWID = CHARTOROWID('AAAAtmAAEAAAAFmAAA');     

15.  CHR(n) : Character with value n

                     Eg: chr(51)=3 

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

                   Eg : concat('df','ee')=dfee

17.  CONVERT (char_to_convert, new_char_set, old_char_set) : 

              Convert a  string from one character set to another.

18.  COS(n): Cosine of number

                  Eg : cos(.3)= 0.95533648

19.  COSH(n)       Hyperbolic Cosine of number

                  Eg : cosh(.3)= 1.04533851

20.  COUNT(*) :     Count the no of rows returned

RBN-INFO-TECH