Sql Functions

Aggregate  functions

• Aggregate functions return a single result row based on groups of rows, rather than on single rows.
• Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses.

They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups.
All aggregate functions except COUNT(*)and GROUPING ignore nulls.

These are Aggregate functions
SUM()
SELECT SUM(salary) as Sum_Sal FROM emp;
AVG()
SELECT AVG(salary) as Avg_Sal FROM emp;
Max()
MAX returns maximum value of expression.
SELECT Max(salary) as Max_Sal FROM emp;
Min()
Min returns minimum value of expression.
SELECT Min(salary) as Min_Sal FROM emp;
Count()
Count returns the number of rows in the query.
SELECT COUNT(*) as total_rec FROM emp;
Example of Aggregate Function using GROUP BY:
To return the minimum and maximum salaries for each department in the emp table,we can use:group by clause
SELECT department_id,MIN(salary),MAX(salary)
FROM emp
GROUP BY dept_id;
To return the minimum and maximum salaries for each department in the emp table where minimum salary should be more than 5000
SELECT department_id, MIN(salary), MAX (salary)
FROM emp
WHERE MIN(salary) > 5000
GROUP BY dept_id;

Character Functions

• It calculates the ASCII equivalent of the first character of the given input string.

ASCII(<Character>)
ascii('A')     would return 65
ascii('a')     would return 97
ascii('a8')     would return 97

CHR(<Character>)
Returns the character equivalent of the given integer.
Example
SELECT CHR(65), CHR(97) FROM dual;
Output : A a

CONCAT(<string1>,<string2>)
This function returns String2 appended to String1.
Example:
SELECT CONCAT('Fname', 'Lname') Emp_name FROM emp;

INITCAP(<String>)
This function returns String with the first character of each word in upper case and rest of all in lower case.
Example:
SELECT INITCAP('oracle tutorial') FROM Dual;
Output :  Oracle Tutorial

instr( string1, string2 [, start_position [,  nth_Appearance ] ] ):
string1 is the string to search.

string2 is the substring to search for in string1.

start_position is the position in string1 from where the search will start. This argument is optional. If not mentioned, it defaults to 1.
The first position in the string is 1. If the start_position is negative, the function counts backward direction

nth_appearance is the nth appearance of string2. This is optional. If not defined, it defaults to 1.

Example
SELECT INSTR('Character','r',1,1) POS1
, INSTR('Character','r',1,2) POS2
, INSTR('Character','a',-1,2) POS3
,INSTR('character','c',) POS4
FROM Dual;
pos1 pos2 pos3 pos4
  4          9          3        6

LENGTH(<Str>)
Returns length of a string
secect length('Sql Tutorial') as len from dual;
O/p len
     12
LOWER(<Str>)
This function returns a character string with all characters in lower case.

UPPER(<Str>)
This function returns a character string with all characters in upper case.

LPAD(<Str1>,<i>[,<Str2>])
This function returns the character string Str1 expanded in length to i characters, using Str2 to fill in space as needed on the left side of Str1.
Example
SELECT LPAD('Oracle',10,'.') lapd_doted from Dual, would return ....Oracle
SELECT  LPAD('RAM', 7)  lapd_exa  from Dual    would return    '    RAM'

RPAD(<Str1>,<i>[,<Str2>])
RPAD is same as LPAD but Str2 is padded at the right side

LTRIM(<Str1>[,<Str2>])
The LTRIM function removes characters from the left side of the character Srting, with all the leftmost characters that appear in another text expression removed.
This function returns Str1 without any leading character that appears in Str2.If  Str2 characters are leading character in Str1, then Str1 is returned unchanged.
Str2 defaults to a single space.
Example
Select LTRIM('datawarehousing','ing') trim1
, LTRIM('datawarehousing   ') trim2
, LTRIM('         datawarehousing') trim3
, LTRIM('datawarehousing','data') trim4 from dual
O/P trim1                                      trim2                                     trim3                                        trim4
       datawarehousing               datawarehousing               datawarehousing                warehousing

RTRIM(<Str1>[,<Str2>])
Same as LTRIM but the characters are trimmed from the right side

TRIM([[<Str1>]<Str2> FROM]<Str3>)

• If present Str1 can be one of the following literal: LEADING, TRAILING, BOTH.
• This function returns Str3 with all C1(leading trailing or both) occurrences of characters in Str2 removed.
• If any of Str1,Str2 or Str3 is Null, this function returns a Null.

Str1 defaults to BOTH, and Str2 defaults to a space character.
Example
SELECT TRIM(' Oracle   ') trim1, TRIM('Oracle ') trim2 FROM Dual;
Ans trim1             trim2
        Oracle          Oracle
It'll remove the space from both string.

REPLACE(<Str1>,<Str2>[,<Str3>]
This function returns Str1 with all occurrence of Str2 replaced with Str3
Example
SELECT REPLACE (‘Oracle’, ’Ora’, ’Arti’) replace_exa FROM Dual;
O/p replace_exa
       Atricle

Essential Numeric Functions
ABS()
Select Absolute value
SELECT ABS(-25) "Abs" FROM DUAL;

  Abs
-------
  15
ACOS ()
Select cos value
SELECT ACOS(.28)"Arc_Cosine" FROM DUAL;
ASIN ()
Select sin value
SELECT ASIN(.6)"Arc_Cosine" FROM DUAL;
ATAN()
Select tan value
SELECT ATAN(.6)"Arc_Cosine" FROM DUAL;
CEIL()
Returns the smallest integer greater than or equal to the order total of a specified orde
SELECT  CEIL(239.8) FROM Dual would return 240

FLOOR()
Returns the largest integer equal to or less than value.

SELECT FLOOR(15.65) "Floor" FROM DUAL;

 Floor
------
15

MOD()
Return modulus value
SELECT MOD(11,3) "Mod" FROM DUAL;

 Modulus
-------
   2

POWER()
SELECT POWER(3,2) "Power" FROM DUAL;

 power
-------
   9
ROUND (number)
SELECT ROUND(43.698,1) "Round" FROM DUAL;

     Round
----------
      43.7

TRUNC (number)
The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.
SELECT TRUNC(12.75,1) "Trunc" FROM DUAL;

  Trunc
----------
  12.75

SELECT TRUNC(12.75,-1) "Trunc" FROM DUAL;

  Trunc
------
  10
Date And Time Function

ADD_MONTHS(date,number_of_month)
SELECT SYSDATE, ADD_MONTHS(SYSDATE,2), ADD_MONTHS(SYSDATE,-2) FROM DUAL;
Result:
SYSDATE     ADD_MONTH ADD_MONTH
-------------    ------------------------------------------
10-Feb-13       10-Apr-13 10-Dec-13

EXTRACT(<type> FROM <date>)
'Type' can be YEAR, MONTH, DAY, HOUR, MIN, SECOND, TIME_ZONE_HOUR, TIME_ZONE_MINUTE, TIME_ZONE_REGION
SELECT SYSDATE, EXTRACT(YEAR FROM SYSDATE)YEAR, EXTRACT(DAY FROM SYSDATE)DAY , EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) TZH FROM DUAL;
LAST_DAY(<date>)
Extract last day of month
Example:
SELECT SYSDATE, LAST_DAY(SYSDATE) END_OF_MONTH  FROM DUAL;
Result: SYSDATE      END_OF_MO
               ----------    ---------------
             10-Feb-13      28-Feb-13

NEXT_DAY(<date>,<day>)
SELECT NEXT_DAY('28-Feb-13','SUN') "FIRST MONDAY OF MARCH”  FROM DUAL;
O/P FIRST MONDAY OF MARCH
               03-Mar-13
ROUND (date[,<fmt>])
SELECT SYSDATE, ROUND(SYSDATE,'MM'), ROUND(SYSDATE,'YYYY') FROM DUAL;
Result:SYSDATE   ROUND(SYS   ROUND(SYS
                                         -----------       ---------------
                                10-FEB-13 01-MAR-13 01-JAN-13
TRUNC(date[,<fmt>])
SELECT SYSDATE, TRUNC(SYSDATE,'MM'), TRUNC(SYSDATE,'YYYY') FROM DUAL;
Result: SYSDATE   TRUNC(SYS    TRUNC(SYS
                    -------      - ------------              --------
                    10-FEB-13 01-FEB-13    01-JAN-13