Oracle Built In Functions

Oracle supports two types of functions:
1. Built In functions(Pre-Defined functions).
2. User Defined Functions.

Built-In Functions:
1. Number Functions: These functions operates on numerical data.
Some of the Number functions are
Greatest(exp1, exp2,…..) Least(exp1, exp2,…..): Greatest returns maximum value among given expression. Whereas Least returns minimum value among given expressions.

select greatest(2,5,8) from dual;

Other functions are ABS(), MOD(), ROUND(m,n), Trunc(m,n),Ceil(), Floor(),……

2. Character or String Functions:
Upper: This is used to convert a string or column values to upper case. Lower converts strings to lower case.

SELECT UPPER(‘pinnapa’) from dual;


INITCAP: It returns initial letter as Capital of each in a word.

SELECT INITCAP(‘you are following pinnapa website’) from dual;

Output: You Are Following Pinnapa Website.
SUBSTR(): This will extract portion of the string within given string based on last two parameters.

SELECT SUBSTR(‘PQRSTUVWX’,2,3) from dual; Output: QRS

SELECT SUBSTR(‘PQRSTUVWX’,-2,3) from dual; Output: WX
SELECT SUBSTR(‘PQRSTUVWX’,-5) from dual; Output: TUVWX

Query to display employees whose name 2nd letter contains AR
SELECT * FROM emp where SUBSTR(ename, 2,2)=’AR’;

INSTR(): This returns number datatype i.e. it returns position of the delimiter. Position of the character, position of the string within given string.

SELECT INSTR(‘ABCDEFG’,’D’) from dual; Output: 4

SELECT INSTR(‘CORPORATE FLOOR’,’OR’, 3, 2) “Instring” FROM dual; Output: 14

Concat(): This is used to concatenate strings. But this function accept two parameters.

SELECT CONCAT(ename, job) from emp;

Translate and Replace: Translate is used to replace character by character where as ‘replace’ is used to replace character by character or string by string.

SELECT REPLACE(job, ‘SALESMAN’, ‘marketing’) from emp;

DATE functions: Oracle default date format is DD-MON-YY.

SYSDATE: This returns current date of the system(server) in oracle date format.
SELECT SYSDATE from dual; Output 24-OCT-2014

ADD_MONTHS(): It is used to add or subtract number of months to the specified date, based on 2nd parameter.
Syntax: ADD_MONTHS(DATE, number)

SELECT ADD_MONTHS(’24-OCT-2014′, 7) from dual; Output: 24-MAY-2015
SELECT ADD_MONTHS(’24-OCT-2014′, -4) from dual; Output: 24-JUN-2014

LAST_DAY(): It returns last day of the specified month

SELECT LAST_DAY(’13-oct-2014′) from dual;

NEXT_DAY(): This returns next occurrences day from the specified day based on the 2nd parameter.

SELECT NEXT_DAY(SYSDATE, ‘SUN’) from dual; Output 21-SEPT-14

MONTHS_BETWEEN(): This returns number of months between two months. This function always returns number datatype.

SELECT ename, hiredate, round(months_between(SYSDATE, hiredate)/12) “Years” from emp;

Date Conversion Functions:
TO_CHAR(): It is used to convert date datatype or number into varchar2 datatype.

Powered by k2schools