BUILT IN FUNCTIONS IN ORACLE

Built In Functions

As in other programming languages, SQL also provides built in functions. Built in functions in SQL are classified into following categories.

Single row functions.
List Functions.
Group Functions.

1) Single Row Functions: The Built in functions of ORACLE that work on each row of the table separately are called as Single Row Functions. Depending on the data type of the values on which Single Row Functions work, they are classified into following five categories.

Character Functions.
Numeric Functions.
Conversion Functions
Date Functions.
Special Functions.


Character Functions :

Built in Single Row Functions of SQL that work on character type of data are called as Character functions. Character functions in SQL are as follows.


UPPER(String or ColName) : This function converts the given string or values of given column in any case to upper case.

LOWER(String or ColName) : This function converts the given string or values of given column in any case to lower case.

INITCAP(String or ColName) : This function converts the given string or values of given column in any case to title case i.e. converts first character in every word to upper case and remaining characters in the word to lower case.

In general above three functions are used to perform case-insensitive queries against database. For example the following query retrieves all managers from emp table with case insensitivity.

Example: Select * from EMP where UPPER(JOB) = ‘MANAGER’;

LPAD(String or Colname, Len, Char ) : This function pads the given char with in the blank spaces to the left of given string or values of given column by assuming the maximum length of string or column as Len.

Following example pads “*” to the left of employee name. In this example, colname is ename, len is given as 10 and char as “*”. Now if ename is “Scott” then ename is five charcaters and we have given the len as 10. Hence it pads five “*” to the left of ename “scott” and result is “*****Scott”.

Example: Select Lpad(Ename,10,”*”) from Emp;

RPAD(String, Len, Char ) : This function pads the given char with in the blank spaces to the Right of given string or values of given column by assuming the maximum length of string or column as Len.

Following example pads “*” to the right of employee name. In this example, colname is ename, len is given as 10 and char as “*”. Now if ename is “Scott” then ename has five charcaters and we have given the len as 10. Hence it pads five “*” to the right of ename “scott” and result is “Scott*****”.

Example: Select Lpad(Ename,10,”*”) from Emp;

LTRIM(String or ColName,[Char]) : Removes any leading blank spaces i.e. blank spaces on left of given string or values of given column if char is omitted. Otherwise removes the given char from left of given string or values of given column.

RTRIM(String or ColName,[Char]) : Removes any trailing blank spaces i.e. blank spaces on right of given string or values of given column if char is omitted. Otherwise removes the given char from right of given string or values of given column.

LENGTH(String or ColName) : Returns the number of characters in the given string or values of given column seperately.

SUBSTR(String or ColName, M, [N] ) : This function extracts ‘ N ’ number of characters from the given string or values of given column starting from ‘ M ’. If ‘ N ‘ is omitted then all charcters starting from ‘ M ‘ will be retrieved.

Example: Select Substr(‘ORACLE CORPORATION’, 8, 4 ) from Dual;

This example returns “CORP” by extracting 4 characters from the given string “ORACLE CORPORATION” starting from the 8th character. If same query is written without giving value 4, then result will be “CORPORATION”.

INSTR(String or ColName, Substring, M, N ) : Finds Nth occurrence of the given substring with in the given string or values of given column starting from ‘M’.

Example: Select INSTR( ‘ORACLE CORPORATION’, ‘OR’, 1, 2 ) from Dual;

This example returns value 9. Because given substring “OR” is started second time at 9th character starting from first(1) character. If the same query is written as follows





Example: Select INSTR( ‘ORACLE CORPORATION’, ‘OR’, 9, 2 ) from Dual;

Then return value is 12. Because given substring “OR” is started second time at 12th character starting from 9th character. If the given substring is not found in the specified ocuurrece, then this function will return the value 0.

REPLACE(String or ColName, Substring, [RepString] ) : Replaces every occurrence of given substring with in the given string or values of given column with Repstring.

Example: Select REPLACE( ‘ORACLE CORPORATION’, ‘ORA’, ‘X’ ) from Dual;
This example will replace every occurrence of given substring “ORA” in the given string “ORACLE CORPORATION” with given replace string “X” and the result will be “XCLE CORPXTION”. If Replace string is omitted, then the given substring will be removed from the given string or values of given column. If replace string “X” is not given in the above example, then result will be “CLE CORPTION”.

12.) TRANSLATE(String or ColName, substring, RepString ) : Replaces every occurrence of individual characters in the given substring within given string or values of given column with corresponding character in the replace string.

Example: Select Translate( ‘ORACLE CORPORATION’, ‘ORA’, ‘XYZ’ ) from Dual;

This example will replace every occurrence of given characters in the substring “ORA” in the given string “ORACLE CORPORATION” with given charcters in replace string “XYZ”. I.e. Character “O” is replaced with “X”, “R” is replced with “Y” and “A” is replaced with “Z” and the result will be “XYZCLE CXYPXYZTIXN”. If corrosponding charcter in Replace string for a charcater in substring is omitted, then the given charcter in substring will be removed from the given string or values of given column. If replace string “XYZ” is given as “XY” in the above example, then result will be “XYCLE CXYPXYTION”.

CONCAT(String1 or Col1, String2 or Col2) : Concatenates String2 or Col2 at the end of String1 or Col1. Instead of concat function, we can use the Concatenation operator “”(Double Pipe Symbol”).

SOUNDEX(String or ColName) : Returns how the given string or values of given column are pronounced. The following example retrives the employee records, whose name pronounces same as “SKOTT”.

Example: Select * from EMP where SOUNDEX(Ename) = SOUNDEX(‘SKOTT’);

ASCII(Char) : Returns ASCII value of given character.

Example: Select Ascii(‘A’) from Dual; ( Returns 65 )

Chr(N) : Returns ascii character for given ‘N’.

Example: Select Chr(65) from Dual; ( Returns “A” )


ii.) NUMERIC FUNCTIONS :

Built in single row functions of SQL that work on numeric type of data are called as numeric functions. Numeric functions in SQL are as follows.

Sin(n) : Returns Sine value of given ‘ n ‘ in Radians.
ASin(n) : Returns Arc Sine value of given ‘ n ‘ in Radians.
3. SinH(n) : Returns Hyperbolic Sine value of given ‘ n ‘ in Radians.
4. Cos(n) : Returns Co Sine value of given ‘ n ‘ in Radians.
5. ACos(n) : Returns Arc Co Sine value of given ‘ n ‘ in Radians.
6. CosH(n) : Returns Hyperbolic Co Sine value of given ‘ n ‘ in Radians.
7. Tan(n) : Returns Tangent value of given ‘ n ‘ in Radians.
8. Atan(n) : Returns Arc Tangent value of given ‘ n ‘ in Radians.
9. TanH(n) : Returns Hyperbolic Tangent value of ‘ n ‘ in Radians.
10. ABS(n) : Returns absolute value of given ‘ n ’. i.e., without any sign.
Following example returns 8 by removing “-“ sign.

Example : Select Abs(-8) from Dual;

11.Exp(n) : Returns ‘ e ’ value raised to the power of ‘ n ’. ‘ e ‘ is exponentiation and its value is 2.718.
Following example returns (2.718)3

Example : Select Exp(3) from Dual;

12.Ceil(n) : Returns the smallest integer among all the values that are greater than given ‘n‘
In the following example, ‘ n ‘ is given as 4.721. Here integers greater than 4.721 will be 5,6,7, and so on. Hence it returns 5, the smallest integer among all values that are greater than 4.721.

Example: Select CEIL(4.721) from Dual;
13. Floor(n) : Returns the largest integer among all the numbers that are less than ‘ n ‘.
In the following example, ‘ n ‘ is given as 4.721. Here integers less than 4.721 will be 1,2,3, and 4. Hence it returns 4, the largest integer among all values that are less than 4.721.

Example: Select Floor(4.721) from Dual;

14. LN(n) : It returns Natural Logarithmic value of ‘ n ‘.
15. LOG(M,N) : It returns base M logarithmic value of given N.
Following example returns Log2(8) i.e. base 2 logorithmic value of 8.

Example : Select Log(2,8) from Dual;

16. MOD(M,N) : Returns the remainder after integer division of M with N.
Following example returns 2. Integer division of 7 with 5 will result 1 and remainder will be 2.
Example : Select Mod(7,5) from Dual;

17. SQRT(N) : Returns Square root value of given ‘ N ’.
Following example will return 5 as square root of 25 is 5.

Example : Select Sqrt(25) from Dual;

18. POWER(M,N) : Returns the value of ‘ M ’ raised to the power of ‘ N ’ i.e. MN
Following example returns 125 i.e. 53.
Example : Select Power(5,3) from Dual;
19. ROUND(N,P) : Rounds the given ‘ N ‘ value to ‘ P ‘ decimal places.
Following example returns 7.64 by rounding 7.6351 to 2 decimal places. Round function increments the value 7.63 to 7.64. Because immediate next digit of 3 is greater than or equal to 5.

Example : Select ROUND(7.6351, 2 ) from Dual;

20. TRUNC(N,P) : Truncates the given ‘ N ’ value to ‘ P ‘ decimal places.
Following example returns 7.63 by truncating 7.6351 to 2 decimal places. Truncate function Does not increment the value 7.63 to 7.64 even immediate next digit of 3 is greater than or equal to 5.

Example : Select Truncate(7.6351, 2 ) from Dual;

21. SIGN(N) : Returns –1 if the ‘ N’ value is negative, 0 ( Zero ) if the ‘ N ‘ value is Zero and +1 if ‘ N ‘ is positive.



iii) CONVERSION FUNCTIONS :

The Built in single row functions of SQL that are used to convert one type of data to another are called as conversion function. Conversion functions in SQL are as follows.


TO_CHAR(n) : Converts the given numeric value to character format.
Following example converts the given numeric value 14 to character format i.e. “14”

Example : Select To_Char(14) from Dual;

2. TO_NUMBER(Char) : Coverts the numeric value in character format to Numeric format.
Following example converts the given numeric value 14 in charcater format i.e. “14” to numeric format i.e. 14

Example : Select To_Number(‘14’) from Dual;

TO_CHAR(Date, Format) : Converts given Date to Character format by using the specified format.
Following example converts the system date to DD-MONTH-YYYY format. If we assume the system date as ‘5-jan-02’ then result will be ‘05-JANUARY-2002’.
Example : Select To_Char(Sysdate, ‘DD-MONTH-YYYY’) from Dual;
Strings that can be used to specify the format and their purpose are as follows
1. D : Returns Day of week.
2. DD : Returns Day of Month.
3. DDD : Returns Day of Year.
4. DY : Abbrivative form of week.
5. DAY : Complete spelling of week. ( Ex: Sunday, Monday, …)
6. SP : Complete spelling of dates (Fourteen).
7. TH : Places ‘Th’ or ‘St’ or ‘Rd’ at the end of day.(3rd, 2nd , 4th etc)
8. MM : Two digital months. ( 01, 02, 02,…12 )
9. MON : Abbrivative form of Month.
10. RM : Roman numerical month. ( i, ii, iii, iv, v,…, XII )
11. MONTH : Complete Spelling of month. ( January, …, December )
12. Y : Last digit of the year.
13. YY : Last two digits of year.
14. YYY : Last three digits of year.
15. YYYY : 4 Digital year.
16. YEAR : Year spelled out.
17. HH / HH12 : Hour of day in 12-hour format.
18. HH24 : Hour of day in 24-hour format.
19. MI : Minute of year.
20. SS : Second of minute.
21. AM / PM : Represents AM or PM.
22. WW : Returns week of YEAR.

TO_DATE(CharDate, Format) : Converts the given date in character format to Date. This function has two arguments. First argument is the date in character format and second one is the format of date, which we have given, in first argument.
Following example converts the given date in character format ‘Fourteen-August-2002’ to date format i.e. 14-Aug-02.

Example : Select TO_DATE(‘Fourteen-August-2002’, ‘DDSP-MONTH-YYYY’) from Dual;

iv) DATE FUNCTIONS:

Built in single row functions of SQL that work on Date type of data are called as Date functions. Date functions in SQL are as follows.

SYSDATE : Returns current system date.
Example : Select Sysdate From Dual;

ADD_MONTHS(Date, n) : Returns a date after adding ‘n’ number of months to the given date. If ‘n’ value is negative then ‘n’ months are subtracted from the given date.
Following example adds two months to the current system date and returns a date. If the current system date is 13-JAN-2003 then this example will return 13-MAR-2003.

Example : Select Add_Months( Sysdate, 2 ) from Dual;

MONTHS_BETWEEN( Date1, Date2 ) : Return the difference between given two dates in number of months.
Following example returns the difference between sysdate and ‘01-MAR-2003’. If the system date is ‘13-JAN-2003’, then result will be 1.51(Estimated). Actually the difference is 1 month 16 days. Value after decimal point is obtained by using “(16/30)*100” Calculation.

Example : Select MONTHS_BETWEEN(Sysdate, TO_DATE(‘01-MAR-03’)) from Dual;

NEXT_DAY(Date, day) : Returns the date of given day after given date.
Following Example returns the date of ‘Sunday’ after sysdate. If system date is ‘13-JAN-2003’ then result will be ‘19-JAN-2003’.

Example : Select NEXT_DAY(Sysdate, ‘Sunday’) from Dual;

LAST_DAY(Date) : Returns the date of last day of the month in given date.
Following example returns the last day of the month in system date. If system date is ‘13-JAN-2003’, then result will be ‘31-JAN-2003’ i.e. the last day of month in sysdate i.e. January.

Example : Select Last_Day(Sysdate) from Dual;

ROUND(Date, format ) : Rounds the given date to given format.
Following example Rounds the system date to 1 month. When we are rounding to a month, then Depending on the day of month i.e. if day is less than or equal to 15, then first day of current month will be the result. If day is greater than 15, then first day of next month will be returned. If sysdate is ‘13-JAN-2003’, then result will be ‘01-JAN-2003’. Because day is ‘01’. If sysdate is ‘17-JAN-2003’, then result will be ‘01-FEB-2003’. Because day is ‘17’.

Example 1 : Select ROUND(Sysdate, ‘MM’) from Dual;

Following example Rounds the system date to 1 Year. When we are rounding to a year, then Depending on month of the year i.e. if month is less than or equal to 6, then first day of current year will be the result. If month is greater than 6, then first day of next year will be returned. If sysdate is ‘13-MAR-2003’, then result will be ‘01-JAN-2003’. Because month is ‘03’. If sysdate is ‘13-JUL-2003’, then result will be ‘01-JAN-2004’. Because month is ‘7’.

Example 2: Select ROUND(Sysdate, ‘YY’) from Dual;

Following example Rounds the system date to 1 Day. When we are rounding to a Day, then Depending on the time of Day i.e. if Time is less than or equal to 12PM, then time part is rounded to “00:00:00AM” of the same date. If Time is greater than 12PM, then, time part is rounded to “00:00:00AM” of the next day of current date. If sysdate is ‘13-JAN-2003 10:23:22AM’, then result will be ‘13-JAN-2003 00:00:00AM’. Because Time is ’10:23:22AM’. If sysdate is ‘13-JAN-2003 01:24:12PM’, then result will be ‘14-JAN-2003 00:00:00AM’. Because Time is ’01:24:12PM’.

Example 3 : Select To_char(ROUND(Sysdate, ‘DD’),’dd-mon-yy hh12:mi:ssAM’) from Dual;

TRUNC(Date, Format) : Truncates the given date to given format.
Following example truncates the system date to 1 month. Unlike Round function, truncate returns the first day of month in given date even date is either less than or equal to 15 or greater than 15. If system date is ‘13-JAN-2003’, then result will be ‘01-JAN-2003’.

Example 1: Select TRUNC(Sysdate, ‘MM’) from Dual;

Following example truncates the system date to 1 Year. Unlike Round function, truncate returns the first day of year in given date even month is either less than or equal to 6 or greater than 6. If system date is ‘13-JUL-2003’, then result will be ‘01-JAN-2003’.

Example 2: Select TRUNC(Sysdate, ‘YY’) from Dual;

Following example truncates the system date to 1 Day. Unlike Round function, truncate truncates the time part to “00:00:00AM” of the given date even the time is either less than or equl to 12PM or greater than 12PM. If the sysdate is ‘13-JAN-2003 01:24:12” then result will be ‘13-JAN-2003 00:00:00AM’.

Example 3: Select TRUNC(Sysdate, ‘DD’) from Dual;

DATE ARITHMETIC

Date + n : Returns a date after adding ‘n’ no. of days to the given date.
Following example adds 3 days to system date. If system date is ‘14-JAN-2003’, then result wil be ‘17-JAN-2003’.

Example : Select Sysdate+3 from dual;

Date – n : Returns a date after subtracting ‘n’ no. of Days from given date.
Following example subtracts 3 days from system date. If system date is ‘14-JAN-2003’, then result wil be ‘11-JAN-2003’.

Example : Select Sysdate - 3 from dual;

3. DATE1 –DATE2 : Returns the difference between given two dates in no. of days.
Following example calculates the difference between system date and ‘20-JAN-2003’. If system date is ‘14-JAN-2003’, then result will be 6. Result may come in float value i.e. 5.45, when the difference is not exactly the multiple of 24 hours. This function calculates the difference from “00:00:00AM” of ‘14-JAN-2003’ to “00:00:00AM” of ‘21-JAN-2003’(we have given the date as ‘20-JAN-2003’ and this day will be completed at “00:00:00AM” of ‘21-JAN-2003’). When the difference is not exactly the multiple of 24, then this function calculates the decimal value as current hour/24*100.

Example : Select sysdate – to_date(‘20-JAN-2003’) from Dual;







SPECIAL FUNCTIONS

DECODE : Built in single row functions of SQL those works on any type of data are called as Special functions. Special functions in SQL are as follows.

Syntax : DECODE(Expression, Result1, ReturnVal1, Result2, ReturnVal2,… else ReturnVal);
This function is equivalent to if statement in programming languages. If the expression returns a value equivalent to Result1, then function returns ReturnVal1, if the expression returns a value equivalent to Result2, then function returns ReturnVal2 and so on.
Following example displays Employee name and Salary. Value of salary column is displayed as ‘On Target’ if employee’s salary is 1500, ‘Below 1500’ if salary is less than 1500 and actual salary i.e. 2000,3000 and so on if salary is greater than 1500.

Sign function returns –1 if the value passed is negative, 1 if the value assed is positive, and 0 if the value passed is zero. If the result of the expression Sign(sal-1500) is –1, then Decode Function will return ‘Below 1500’. If the result is zero, then decode function will return ‘on target’. If the expression sign(sal-1500) does not return either –1 or zero, the decode function will return the salary of employee.

Example : Select Ename, DECODE(Sign(Sal-1500), -1, ‘Below 1500’, 0, ‘On Target’, Sal) Salary from EMP;

Using Case : As of Oracle 9i, you can use CASE function in place of DECODE. The CASE function uses the keywords when, then, else, and end to indicate the logic path followed, which may take the resulting code easier to follow than an equivalent DECODE.

Following example displays Employee name and Salary. Value of salary column is displayed as ‘On Target’ if employee’s salary is 1500, ‘Below 1500’ if salary is less than 1500 and actual salary i.e. 2000,3000 and so on if salary is greater than 1500.

Example : Select Ename, case sign(sal-1500)
when 0 then 'On Target'
when -1 then 'Below target'
else to_char(sal)
end "salary" from emp

NVL(Col, Val ) : Replaces Null value in the given column with given val.
Following example displays Employee name, Salary and Total Remuneration for all employees. Total Remuneration is calculated by the formula ( (Sal * 12 ) + Comm ). Any arithmetic operation with null value result null value. As only Sales employees are having commission, For all remaining employees the total remuneration will be Null. To solve this problem we will write the formula to calculate total remuneration as ((Sal * 12) + nvl(Comm,0)). This replaces the null value of comm column with zero. Now arithmetic operation is not performed against null value. Hence the result will be correct.

Example : Select Ename, Sal, (Sal*12+NVL(Comm, 0)) TotalRem from EMP;


LIST FUNCTIONS

The built in functions of SQL that work on a given list of values are called as List Funtions. The list functions in SQL are as follows.

1. LEAST(Val1, Val2, …, Valn) : Returns the smallest value in given list of values.

Example 1 : Select Least(10, 15, 120, 380, 0 ) from Dual; ( Result is 0 )
Example 2 : Select Least( ‘ORACLE’, ‘MICROSOFT’, ‘SUN’, ‘ADOBE’ ) from Dual; (Result is ADOBE)

Example 3 : Select Least(To_Date(‘01-JAN-03’), To_Date(‘12-DEC-02’),To_char(‘31-DEC-03’)) from Dual; (Result is ‘12-DEC-02’)

2.GREATEST( Val1, Val2,…,Valn) : Returns the largest value in given list of values.

Example 1 : Select Greatest(10, 15, 120, 380, 0 ) from Dual; ( Result is 380 )

Example 2 : Select Greatest( ‘ORACLE’, ‘MICROSOFT’, ‘SUN’, ‘ADOBE’ ) from Dual; (Result is SUN)

Example 3 : Select Greatest(To_Date(‘01-JAN-03’), To_Date(‘12-DEC-02’),To_char(‘31-DEC-03’)) from Dual; (Result is ‘31-DEC-03’)

GROUP FUNCTIONS

Built in functions of SQL those works on a group of values are called as Group functions. Group functions in SQL are as follows.

1. SUM(Col) : Returns the sum of all values in the given column.
Following example finds the total salary drawn by all employees.

Example : Select SUM(Sal) from EMP;

AVG(Col) : Returns average of all values in the given column.
Following example finds the average salary drawn by all employees.

Example : Select AVG(Sal) from EMP;

3. MAX(Col) : Returns maximum value of the values in the given column.
Following example finds the maximum salary among the salaries of all employees.

Example : Select MAX(Sal) from EMP;

MIN(Col) : Returns the minimum value of the values in the given column.
Following example finds the minimum salary among the salaries of all employees.

Example : Select MIN(Sal) from EMP;

5. COUNT(Col) : Returns the count of Non Null values in the given column.
Following eample finds the number of employees drawing Commission.

Example : Select COUNT(COMM) from EMP;

6. COUNT(*) : Returns the total number of rows in the given table.
Following eample finds the total number of employees in Emp Table.

Example : Select COUNT(*) from EMP;


7. STDDEV(Col) : Returns the standard deviation for all values of the given column.
Following eample calculates the Standard deviation of salaries of all employees.

Example : Select STDDEV(SAL) from EMP;

VARIANCE(Col) : Returns the variance for all values of the given column.
Following eample calculates the Variance of salaries of all employees.

Example : Select VARIANCE(SAL) from EMP;

GROUP BY : Group by clause is used to divide the rows of a table into groups and apply group functions on each group separately.
Following example finds the maximum salary in each deartment.

Example 1 : Select Deptno, Max(Sal) from EMP Group By Deptno;

Following example finds the average Salary for each job type.

Example 2 : Select Job, AVG(Sal) from EMP Group By Job;

Rule : When we are using group by clause in a select statement, then all column names in the selection list except group functions must be included in group by clause.
Following example finds the total salary drawn by each job type in each department. In this example Deptno and job, two columns are listed in selection list along with the group function sum. As per the rule, we must include these two columns in, group by clause.

Example 3 : Select Deptno, Job, Sum(Sal) from emp group by Deptno, Job;

HAVING : When we have to check a condition that includes a group function, then instead of where clause we must use Having clause.
Following example finds sum of salaries of all employees of the departments that have more than 3 employees.

Example 1: Select Deptno, Sum(sal) from EMP Group By Deptno Having COUNT(*) > 3;

Following example finds the number of employees in departments whose average salary is more than 3000.

Example 2: Select Deptno, Count (*) from EMP Group By Deptno Having AVG(Sal) > 3000;

Rule : If we are using having clause in a select statement, then a corresponding Group By clause is MUST in that select statement.