Functions in Oracle:
Oracle Functions are is used to perform tasks or some complex calculation and must return
value.
Oracle supports two types of functions. Those are as follows.
1. Pre-define / Built in functions (use in SQL & PL/SQL)
2. User define functions (use in PL/SQL)
Pre-define functions / Built-in functions in Oracle
The functions which are already defined by the oracle system and ready to be used by the user
or developer are called system-defined functions or pre-defined functions. These are again
classified into two categories.
1. Single row functions (scalar functions)
2. Multiple row functions (grouping functions)
Single Row Functions in Oracle:
These functions are returning a single row (or) a single value. Following are the examples.
1. Numeric functions
2. String functions
3. Date functions
4. Conversion functions
How to call a function in Oracle?
Following is the syntax to call a function in Oracle:
What is Dual in Oracle?
1. It is a pre-define table in oracle.
2. It is having single column & single row
3. It is called a dummy table in oracle.
4. It is used for testing functions (pre-define & user define) functionalities.
Structure of Dual table:
===================
DESC DUAL;
Data of DUAL table:
===================
SELECT * FROM DUAL;
String Functions/Character functions
===============================
1)UPPER()
========
It is used to convert a string or column values into a uppercase
Syntax
======
UPPER()
Select upper('abc') from dual;
select upper(ename) from emp;
2)Lower
=======
It is used to convert a string or column values into a lowercase
Syntax
======
Lower()
3)Initcap
========
It converts first letter is capital and then all remaining letters are small.
Syntax
======
Initcap()
select initcap(ename) from emp;
select initcap('ab cd ef') from dual;
4)Length()
=========
It returns number data type. i.e., it returns total length of the string including spaces.
select length(job),job from emp;
SUBSTR()
========
It will extract part of the string. Within given string. based on last 2 parameters.
It will display characters.
Syntax
======
SUBSTR(string,start,[length]);
select substr('ABCDEFGH',2,3) FROM DUAL;
select substr('ABCDEFGHI',-3,2) FROM DUAL;
SELECT * FROM EMP WHERE ENAME LIKE 'S%';
SELECT * FROM EMP WHERE SUBSTR(ENAME,2,2)='LA';
INSTR()
====
This function always returns number datatype.
It returns position of the character ,position of the string.
Syntax
======
Instr(string,char,[start,occurance]);
SELECT INSTR('HELLO WELCOME','O') FROM DUAL;
SELECT INSTR('HELLO WELCOME','E',-1,1) FROM DUAL;
LTRIM
======
It is used to remove specified characters on the left side of the given string.
Syntax
=======
LTRIM(STRING1,STRING2)
SELECT LTRIM('SSSABCDEFGSSS','S') FROM DUAL;
SELECT JOB,LTRIM(JOB,'CSMP') FROM EMP;
RTRIM
======
It is used to remove specified characters on the RIGHT side of the given string.
Syntax
=======
RTRIM(STRING1,STRING2)
SELECT LTRIM('SSSABCDEFGSSS','S') FROM DUAL;
SSSABCDEFG
SELECT JOB,RTRIM(JOB,'NKRT') FROM EMP;
SALESMAN SALESMA
CLERK CLE
SALESMAN SALESMA
MANAGER MANAGE
SALESMAN SALESMA
MANAGER MANAGE
MANAGER MANAGE
ANALYST ANALYS
SALESMAN SALESMA
CLERK CLE
CLERK CLE
ANALYST ANALYS
CLERK CLE
TRIM
=====
It is used to remove left and right side specified characters.
Syntax
=======
TRIM(Character from ‘string’)
select trim('S' FROM 'SSSABCDEFGSSS') FROM DUAL;
ABCDEFG
SELECT TRIM(TRAILING 'S' FROM 'SSSABCDEFGSSS') FROM DUAL;
SELECT TRIM(LEADING 'S' FROM 'SSSABCDEFGSSS') FROM DUAL;
Note
====
We can also convert TRIM() Into LTRIM By using leading clause whereas we can also convert
TRIM() Into RTRIM() BY using trailing clause.
select length(trim(' abc ') ) from dual;
TRANSLATE(),REPLACE()
=====================
Translate() is used to replaces character by character whereas replace is used to replaces
character by string or replaces string by string.
This function accepts 3 parameters.
Syntax
=====
Translate(strng1,strng2,strng3)
select translate ('INDIA','IN','XY') FROM DUAL;
XYDXA
REPLACE()
=========
This function accepts 3 parameters.
Syntax
=====
Translate(strng1,strng2,strng3)
SELECT JOB,REPLACE(JOB,'SALESMAN','MARKETING') JOB1 FROM EMP;
SALESMAN MARKETING
CLERK CLERK
SALESMAN MARKETING
MANAGER MANAGER
SALESMAN MARKETING
MANAGER MANAGER
MANAGER MANAGER
ANALYST ANALYST
SALESMAN MARKETING
CLERK CLERK
CLERK CLERK
ANALYST ANALYST
CLERK CLERK
PRESIDENT PRESIDENT
LPAD()
=====
This function are used to fill string with a character.
It will fills remaining spaces with specified character on the left side pf the given string.
This function accepts 3 parameters and also here 2 nd parameter returns total length of the
string.
Syntax
========
LPAD(COLNAME,TOTAL LENGTH,’FILLED CHARACTERS’)
OR
LPAD((STRING,LENGTH,CHAR)
SELECT LPAD('ABCD',10,'@') FROM DUAL;
@@@@@@ABCD
RPAD()
=====
It will fills remaining spaces with specified characters on the right side of the given string
SELECT RPAD('ABCD',10,'@') FROM DUAL;
ABCD@@@@@@
Concat(strng1,strng2)
==============
It is used to concatinate 2 strings.
1)Null
2)NVL()
3)NVL2()
1) NULL
=======
Null is unknown [Link] is same as 0 or zero.
In all relational databases any arthemetic operations performed on null values again it will
becomes null.
Ex:
====
Null+50=null
Overcome this problem oracle provided nvl functions.
NVL()
=====
NVL Function always accepts 2 parameters.
Syntax
======
Nvl(exp1,exp2)
Here exp1,exp2 must belongs same datatype.
Here if exp1 is null then it will returns exp2
Whereas if exp1 is not null it will returns exp1.
SELECT NVL(NULL,0) FROM DUAL;
SELECT NVL(20,40) FROM DUAL;
SELECT NVL(0,40) FROM DUAL;
SELECT ENAME,SAL,COMM,SAL+NVL(COMM,0) AS TOTAL_SALARY FROM EMP;
SELECT ENAME,SAL,COMM,NVL(COMM,0) FROM EMP;
NVL2()
======
It will accepts 3 parameters.
SYNTAX
=======
Nvl2(exp1,exp2,exp3)
Here exp1 is null then it will returns exp3.
Whereas if exp1 is not null then it will returns exp2. select nvl2(10,20,30) from dual;
select nvl2(null,20,30) from dual;
Date functions
============
In oracle default date format is DD-MON-YY
Oracle having following predefined date functions. These are
1)sysdate
2) current_date
3)current_timestamp
4)systimestamp
5)add_months
6)months_between
7)last_day
8)next_day
Sysdate
---------
It will returns the current date of the system in oracle date format.
SELECT SYSDATE FROM DUAL;----------> 03-04-25
ADD_MONTHS()
===============
It is used to add or subtract number of months from the specified date based on second
parameter. this function accepts 2 parameters.
Syntax
======
ADD_MONTHS(DATE,NUMBER)-------------NUMBER(+VE OR -VE)
LAST_DAY()
==========
It returns last date of the month based on passed date but this function accepts only parameter.
Syntax
=======
Last_day(date)
select last_day(sysdate) from dual;---- 30-04-25
Date arthemetic
===============
1)date+number(valid)
2)date-number(valid)
3)date1+date2(not valid)
4)date1-date2(valid)
select sysdate from dual;
select sysdate+1 from dual;
select sysdate-1 from dual;
select sysdate+sysdate from dual;
select sysdate-sysdate from dual;
Display 1st day of the next month?
select last_day(sysdate)+1 from dual;------ 01-05-25
select next_day(sysdate,'thursday') from dual;
Date Conversion Functions
==========================
Oracle provided following date conversion functions. These are
1)TO_CHAR()
2)TO_DATE
3)TO_NUMBER
TO_CHAR()
=========
It is used to convert oracle date type into character type.
Syntax
======
To_char(date,format)date means-----sysdate
Note
=====
To_char character formats are case sensitive formats.
Formats
======
YYYY-----2025
YY-----------25
CC(Centuary)---25
AD/BC---------- AD Year/BC Year
MM--------------01
MON/mon-------JAN/Jan
MONTH/month------JANUARY/January
D--------Day of the week
DD------Day of the month
DDD----Day of the year
1----sunday
2------monday
3-----Tuesday
4-----wednesday
5------thursday
6-----friday
7-----Saturday
dy/DY-------tue/TUE
DAY/day----TUESDAY/tuesday
Q QUARTER(1-4)
1 JAN TO MAR
2 APR TO JUN
3 JUL TO SEP
4 OCT TO DEC
W--------week of the month
WW-------week of the year
HH-----hour
HH24---hour part in 24 hrs format
mi------MINUTES
ss-------seconds
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'YY') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'day') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'w') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'ww') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI:SS') FROM DUAL;
Display employees joined in 1981 year?
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')=1981;
Display employees joined in 1980,1983,1985?
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY') IN(1980,1983,1985);
Display employees who joined on SUN?
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'DY') ='SUN'
Display employees joined in 2nd quarter of 1981 year?
TH---- SUFFIX TO A NUMBER
SP--------NUMBER TO BE SPELL OUT
SPTH
THSP-----same as SPTH
SELECT TO_CHAR(SYSDATE,'DDSPTH') FROM DUAL;
TO_DATE()
=========
It is used to convert oracle date type into date string
Syntax:
======
To_date(date string,format)
Example:
=======
select to_date('07-04-2025','MM-DD-YYYY') FROM DUAL;
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(TO_DATE('15-AUG-1947'),'DAY') FROM DUAL;
SELECT TO_DATE('20-MAR-22','DD-MON-YY')+30 FROM DUAL;
FORMATS
===========
0 represents a digit
9 represents a digit
, thousand separator
L currency symbol
. decimal separator
C currency
Converting number to char type
==========================
Numbers converted to char type to display numbers in different formats
To_char(number,format)
SELECT TO_CHAR(1234,'000000') FROM DUAL;
SELECT TO_CHAR(1234,'9,999') FROM DUAL;
SELECT ENAME,SAL FROM EMP;
SELECT ENAME,SAL,TO_CHAR(SAL,'C99,999') AS SAL FROM EMP;
Mathemetical Functions
====================
1)power()
2)abs()
3)sqrt()
4)sign()
5)mod()
select power(3,2) from dual;
select sqrt(16) from dual;
select abs(-10) from dual;
mod()
=====
it returns remainder
select mod(10,2) from dual;
select sign(10) from dual;
select sign(-10) from dual;