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           ___ 

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

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

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

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

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

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

                          

8. 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)

9. 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)

10.  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)

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

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

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

14.  NVL2: This function  will returns the 2^nd  value if the 1^st value is not null

                  Or it will return the 3^rdvalue if the 1^st 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

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

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

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

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

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

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

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