add

About Me

My photo
Oracle Apps - Techno Functional consultant

Monday, September 9

Oracle SQL Functions


SQL functions are divided into 2 categories as follows:

  • Single row function
  • Group function

1.    Single row function :
Single row function will take single input and gives a single output.
   
Ø  Single row functions are divided into five categories:

ü  Number function (arithmetic functions) :
 These functions are used to process the numbers
         
v  Numeric function are  as fallows:

  1. Abs:  Will return the absolute (unsigned) value.
               For this function a single parameter is required.

    Syntax:ABS (NUMBER)

         Ex:  Select abs (2), abs (-2), abs (0), abs (null) from dual

         Output:           2          2               0           ___ 

  1. Sign:  Will give the sign of the given number 
                (   0(zero),    1(positive), -1(negative)   ).
                For this function a single parameter is required.

      Syntax: SIGN (NUMBER)

Ex: Select sign (100) from dual

Output:      (+) 1

  1. Sqrt:  Will give the square root of the given value.
                  (In this value must be positive)
                For this function a single parameter is required.

      Syntax: SQRT (NUMBER)

Ex:  Select sqrt (81) from dual
Output:             9

  1. Mod:  will give the reminder.
                For this function two parameters are required.
                And in this the first value is divided by the next value.

       Syntax: MOD (VALUE, DIVISOR)

        Ex: Select mod (8, 3) from dual
        Output:           2

  1. Nvl:  will  substitute the  given value in the place of  null value of the given 
                Column                  
                 For this function two parameters are required.
                   One is substitute value and column name.

      Syntax: NVL (COL, SUBSTITUTE VALUE)

            Ex: Select nvl (comm., 100) from EMP
           Output:           100

  1. Power:   This will give the power value
                      For this function two parameters are required (Value, power)

       Syntax: POWER (VALUE, EXPONENT (POWER))

       Ex:   select power (5, 5) from dual
     Output:                  3125

  1. Ceil:    will give the whole number that is greater than or equal  to the    
                       Specified value
                  For this function a single parameter is required.

      Syntax: CIEL (VALUE)

            Ex: select ceil (months_between (sysdate, hiredate)/12) as exp from EMP
           Output:           31    (30.1332226666)
                          
  1. Floor:  will give the whole number that is less than or equal  to the    
                       Specified value
                  For this function a single parameter is required.
      Syntax: FLOOR (VALUE)

           Ex:      select floor (sal /31) as Asal from EMP
           Output:             84(84.5806451)


  1. Round:  This  function will round  the given number  to the specified
                         Places
                   For this function two parameters are required

              Syntax: ROUND (VALUE, PRECISION)

    •  In this function  if the  second (PRECISION ) value is positive then
Then it gives number of places after decimal

           Ex:  select round ((sal/31), 2) as d sal from EMP
             Output:                84.58(84.580645161)

    • If the precision value is negative  then it  will round up  the given value
-1        then it round up to the nearest tens.
-2        then it round up to the nearest hundreds.
-3         then it round up to the nearest thousands.
And so on.

        Ex:    select round ((sal/31),-2) from EMP
       Output:                100(84.580645161)



  1.  Trunc: This will truncate (just remove) the specified precision / places
                    For this function two parameters are required

        Syntax: TRUNC (VALUE, PRECISION)

    •       In this function  if the  second (precision ) value is positive then
Then it gives number of places after decimal

        Ex: select trunc (sal*40/100+88.123, 2) as hra from EMP
       Output:              1136.92(1136.923)

    • If the specification value is negative  then it  will truncate the given value up to specified places ( it just replace with 0(zero))

              Ex: select trunc (sal*12,-2) as Asal from EMP
             Output:             31400 (31464)







  1. Greatest: This function  will give the greatest    value in the given values
                       This function accepts any number of parameter

        Syntax: GRETEST (VALUE1, VALUE2, VALUE3, VALUE4 ….VALUEN)

         Ex:  select greatest (12,21,45,54,34,46,7,45,65,99,99) from dual
        Output:                    99
           
                Or
               Select greatest (-12,-21,-45,-54,-34,-46,-7,-45,-65,-99,-99) from dual
        Output:                   -7

  1. Least: This function  will give the least(smallest)    value in the given               
                  Values
                  This function accepts any number of parameter

         Syntax: LEAST (VALUE1, VALUE2, VALUE3, VALUE4 ….VALUEN)

           Ex:  select least (12,21,45,54,34,46,7,45,65,99,99) from dual
        Output:                    7
                Or
               Select least (-12,-21,-45,-54,-34,-46,-7,-45,-65,-99,-99) from dual
        Output:                    -99


  1. Coalesce: This function will return the  first not null  value
                             This function accepts any number of parameter

     Syntax: COALESCE (VALUE1, VALUE2, VALUE3, VALUE4 ….VALUEN)

                   Ex: select coalesce(1,2,3,4,5,6), coalesce(null,null,8,null) from dual
            Output:                      1                                           8   

  1.  NVL2: This function  will returns the 2nd  value if the 1st value is not null
                  Or it will return the 3rdvalue if the 1st value is null.
                  This function requires three parameters  

       NVL2 (VALUE1, VALUE2, VALUE3)

         Ex: select nvl2 (com, sal, empno) from EMP
      Output:           7839 (where comm. Is null)






ü   Character function (string manipulation function) :
           
         These functions will process the character or (strings).

  1. Character functions are divided as fallows:

1.    Initcap: This function will capitalize the first letter in the given strings

    SYNTAX:  INITCAP (STRING)

      Ex: select initcap (ename) from EMP
      Output:           King                
        
2.    Upper: This function will convert the string into uppercase

    SYNTAX:  UPPER (STRING)

              Ex:  select upper (ename) from EMP
             Output:              KING

3.    Lower: Will convert the string into lowercase

   SYNTAX:  LOWER (STRING)

               Ex: select lower (ename) from EMP
              Output:     king

4.    Length: will give the length(the space used  in terms of number )  of the given string

     SYNTAX:  LENGTH (STRING)

             Ex: select length (dname) from dept
              Output:          14   

5.    ASCII: will return the (ASCII code) decimal representation in the database character set .of the first character of the string.
The standard numbers are as fallows:
a to z = 65 to 90, A to Z = 97to 122, and 0 to 9 = 48 to 97

      SYNTAX:  ASCII (STRING)

             Ex: select ASCII (ename) from EMP
            Output:        75 (of k)

6.    Chr:  This function will returns the ascii character to the given ascii code(number)

    SYNTAX:  CHR (NUMBER)

              Ex: select Chr (97) from dual
             Output:            a

7.    Substr: This will return the specified substring from the given string
                             This function requires 3 parameters to process.

    SYNTAX:  SUBSTR (STRING, START_CHR_COUNT, NUM_OF_CHR)

EX: select substr(‘computer’,3),substr(‘computer’,3,5), substr(‘computer’,3,7)from emp    Output:     mputer                        mput                  mputer
       
8.     Instr:This function will helps us to search set of characters through a string

       SYNTAX: INSTR (STRING, SEARCH STRING, START_CHR_COUNT,   
                         OCCURRENCE)    
     
            EX: SELECT INSTR (‘IRONMAN3’,’R’, 1, 2) FROM DUAL
            Output:                              7                       
          
9.    Lpad: will allow padding the left side column with any set of characters.
   It means it fills the blank spaces with the specified set of characters to     the left side column up to specified length.

SYNTAX: LPAD (STRING, LENGTH, PADDING CHAR)

Ex: select lpad (sal, 10,’>’) from EMP
Output:  >>>>>>2622       

10. Rpad: will allow us padding the right side column with any set of     characters. that is it fills the blank spaces with the specified set of
          Characters to the right side column up to specified length

SYNTAX: RPAD (STRING, LENGTH, PADDING CHAR)

            Ex: select rpad (sal, 10,’<’) from EMP
          Output:         2622<<<<<<



11. Ltrim: This function will trim (remove) the specified character from extreme left side of the given string.  
                         By default second parameter is a blank space.

            SYNTAX: LTRIM (STRING, UNWANTED CHARACTER STRING)

           Ex: select ltrim (‘King’,’K’) from dual
          Output:             ing

12. Rtrim: This function will trim (remove) the specified character from    extreme right side of the given string.  
                       By default second parameter is a blank space.

            SYNTAX: RTRIM (STRING, UNWANTED CHARACTER STRING)

           Ex: select rtrim (computersss,’s’) from dual
           Output:        computer

13.Concat: This function is used to join two strings

  SYNTAX: CONCAT (STRING1, STRING2)

   Ex: select concat (‘HAPPY’, ‘NEW YEAR’) from dual
  Output:            HAPPY NEWYEAR

14. Translate: This function will replace the set of characters ,  character by character

      SYNTAX:  TRANSLATE (STRING, OLD CHR, NEW CHR)

      EX: select translate (sal, ‘1234’, abcd) from EMP
Output:                                   b6bb
              

15.  Replace: This function will  replace the set of characters, string by string  (the whole string will be replaced)

    SYNTAX:  REPLACE (STRING, OLD STR, NEW STR)

    EX:  select replace ( ‘NARAYANMURTI’,’INDIA’,’MURTI’) from dual
Output:              INDIAMURTI






16. Decode:   This function will  help to substitute value by value

   Syntax: decode (value (column name), if1, then1, if2, then2…..else)

           Ex: select decode (sal, (<1000), low, (>4000), high, medium) from EMP
          Output:                      low              high, (for other numbers) medium

17. Soundex:  This is used to find words that sound  like other words,              exclusively used in where clause
    
  Syntax: soundex (string)

   Ex: select * from EMP
      Where soundex (ename) = soundex (‘FORD’)


Output: 7928 ford. Anylist, 7566, 12-03-1981, 1300, __, 20






























ü  Date function: 


     These functions are used to process the dates.



Oracle default date format is (DD-MM-YY)
We can change the format when required.



v  Date functions are divided  as fallows:



  1. SYDSDATE:  This function is used to access the current  date and time
    
    Ex: Select sysdate from dual
 Output:      1/4/2012 2:49:42 PM


  1. Current date:  This will give the current date in the session’s time zone.

Ex: select current date from dual;
 Output:        1/4/2012 2:50:53 PM


  1. Months_betweeen: This function will give the number of months between the 2 dates given.

Syntax: months_between (date1 (recent), date2 (old))

Ex:  Select months_between (sysdate, hiredate) from EMP   
 Output:      361.600641054361         


  1. Add _months:     This  is used to add number of  months  to an given date

Syntax:    add_months (date, number)

 Ex:  select add_months (hiredate, 5) from EMP
Output:         4/17/1982

  1. Last_day:  This function is  used to get the last day of the  given  month

       Syntax: Last_day (date)

      Ex: select last_day (sysdate) from EMP

        Output:   1/31/2012 2:54:55 PM


  1.  Next_day:  This  function is used  get the date  of a  given day after the given date
       
         Syntax:  Next_day (date,’ day’)

            Ex: select next_day (hiredate,’sunday’) from EMP
            Output:          11/22/1981






























Data conversion function:

                                               These functions are used to convert the data from one format to other format.

1 To_char:

This function is used to convert the number or date to character    format

Syntax:  to_char (number / date, format)

Ex: select to_char (hiredate, ‘day’) from EMP
Output:        Tuesday 

2.     To_number:  

    This function is used to convert the character or string into            number format

Syntax:  TO_ number (char, format)

Ex: select to_number (‘1, 23,456’,’99, 99,999’) from dual
Output:                  123456

  1. To_date:

 This function is used convert the given character string into date format

    Syntax: To_Date (char, format)

Ex: select to_date (’12-jan-2011’) from EMP
Output:                          1/12/2011








  1.  Group Function:

       Here in this Group function it set (group) of input and   Gives a single out put
    
       Fallowing are the group functions:

1)   Max: It is used  to  get the max  value  in the given (column)set of values

Syntax:  max (column name)

Ex: select max (sal) from EMP
Output:            5000

     
2)   Min: it is used to get the min value in the given (column)set of values

Syntax: min (column name)

Ex: select min (hiredate) from EMP
Output:       2/20/1981
      

3)   Avg:  it is used to get the average value of the given (column ) set of values  and (it ignores the null value )

Syntax: avg (column name)

Ex: select avg (sal) from EMP
Output:         2882.85714285714

4)    Sum: it is used to  get the total  of the given ( column ) set of values

    Syntax:  Sum (column name)

     Ex:   select sum (sal) from EMP
      Output:      40360

5)   Count:  it is used  to count the number of rows  in the given set of values

     Syntax:  count (column name)

    Ex: select count (comm) from EMP
     Output:                       4  


No comments: