Oracle Basic Functions Tutorial
I have tried to explain oracle basic functions with descriptive and same function with demonstration. Well this is
not the complete list of all functions so you should consider it a very basic document for reference or practice
purpose. I have demonstrated all the functions using Oracle 9i release 9.2.0.6. I am expecting your input and
suggestions to improve the material of this document. Thanks
Author: Sikandar Hayat
sikandar_h@hotmail.com
www.erpstuff.com
Pakistan
Version: 1.0
Last updated: 18/04/2006
# Function Description Demonstration
1 ABS To get absolute value of any SQL > select abs(-10) from dual;
number.
ABS(-10)
----------
10
2 ADD_MONTHS Add number of months is SQL > select sysdate, add_months(sysdate, 2) from
the specified date and get dual;
the next date.
SYSDATE ADD_MONTH
--------- ---------
10-APR-06 10-JUN-06
3 ASCII This will return the ASCII SQL > select ascii('A') from dual;
value of the character
specified. ASCII('A')
----------
65
4 AVG To get the average SQL> select avg(sal) from emp;
AVG(SAL)
----------
2073.21429
5 CEIL To get the next greater SQL> select ceil(23.8) from dual;
integer.
CEIL(23.8)
----------
24
6 CHR It is reverse of ASCII SQL> select chr(65) from dual;
function explained above,
used to convert ASCII to C
its character. -
A
7 COALESCE
8 COMPOSE
www.erpstuff.com admin@erpstuff.com 1/7
9 CONCAT To concatenate two SQL> select concat('Pak','istan') from dual;
strings. You can use two
different table fields to CONCAT('
concatenate. --------
Pakistan
10 CONVERT To convert from one SQL> SELECT CONVERT('Ä Ê Í Õ Ø A B C D E ',
character set to another. 'WE8ISO8859P1', 'US7ASCII')
2 FROM DUAL;
CONVERT('ÄÊÍÕØABCDE','WE8
-------------------------
¿¿ ¿¿ ¿¿ ¿¿ ¿¿ A B C D E
SQL>
11 COUNT Count number of records SQL> select count(*) from emp;
in a table.
COUNT(*)
----------
14
SQL> select deptno, count(*) from emp group by
deptno;
DEPTNO COUNT(*)
---------- ----------
10 3
20 5
30 6
12 CURRENT_DATE Returns date as per SQL> select current_date from dual;
session time zone.
CURRENT_DATE
--------------------
10-APR-2006 11:04:23
13 DBTIMEZONE
14 DECODE Decode is very useful SQL> select deptno, decode(deptno, 20, 'Dept code is
function and is equivalent 20', 30, 'Dept code is 30', 'Other') from emp;
to if..elsif. The maximum
no of components DEPTNO DECODE(DEPTNO,2
including expr, searches, ---------- ---------------
results and default is 255. 20 Dept code is 20
30 Dept code is 30
30 Dept code is 30
20 Dept code is 20
30 Dept code is 30
30 Dept code is 30
10 Other
20 Dept code is 20
10 Other
30 Dept code is 30
20 Dept code is 20
30 Dept code is 30
20 Dept code is 20
10 Other
14 rows selected.
15 FLOOR Reverse of CEIL SQL> select floor(23.7) from dual;
FLOOR(23.7)
-----------
23
www.erpstuff.com admin@erpstuff.com 2/7
16 GREATEST It will return the greatest SQL> select greatest('A','C','B') from dual;
string or number from the
specified list. G
-
C
SQL> select greatest(1,3,2) from dual;
GREATEST(1,3,2)
---------------
3
17 INITCAP It will caps the first SQL> select initcap('sikandar hayat') from dual;
character.
INITCAP('SIKAN
--------------
Sikandar Hayat
18 INSTR Very useful function SQL> select instr('Pakistan','i') from dual;
specially while working
with strings, it will return INSTR('PAKISTAN','I')
the position of string in ---------------------
another specified string. 4
19 LAST_DAY It will return last day of SQL> select last_day(to_date('15/02/2006')),
month of specified date. last_day(sysdate) from dual;
LAST_DAY(T LAST_DAY(S
---------- ----------
28/02/2006 30/04/2006
20 LAST_VALUE
21 LEAD
22 LEAST
23 LENGTH It will provide the length of SQL> select length('Pakistan') from dual;
string.
LENGTH('PAKISTAN')
------------------
8
24 LOWER To convert upper case SQL> select lower('USA') from dual;
letters to lower.
LOW
---
Usa
25 LPAD It will left pad the specified SQL> select lpad('Islamabad', 12, '*') from dual;
character within the length
specified. LPAD('ISLAMA
------------
***Islamabad
SQL> select rpad('123456', 12, '0') from dual;
LPAD('123456
------------
000000123456
26 LTRIM This function is used to SQL> select ltrim('000123','0') from dual;
remove character from left
side of string specified. LTR
---
123
www.erpstuff.com admin@erpstuff.com 3/7
27 MAX It will give maximum SQL> select max(sal) from emp;
number.
MAX(SAL)
----------
5000
28 MIN Reverse of MAX as it will SQL> select min(sal) from emp;
give minimum number.
MIN(SAL)
----------
800
29 MOD It will return remainder SQL> select mod(3,2) from dual;
after dividing first number
with 2nd. MOD(3,2)
----------
1
SQL> select mod(55,10) from dual;
MOD(55,10)
----------
5
30 MONTHS_BETWEEN To get number of months SQL> select months_between(to_date('01/03/2007'),
between two dates. to_date('01/01/2007')) Months from dual;
MONTHS
----------
2
31 NEXT_DAY To get the next date of SQL> select to_char(sysdate, 'day dd/mm/yyyy') from
day specified. Like in dual;
example the it is
Wednesday 12/04/2006 TO_CHAR(SYSDATE,'DAYDD/MM/YYYY')
and in second query I got --------------------------------------
the date when next wednesday 12/04/2006
Wednesday will come.
SQL> select next_day(sysdate,'wednesday') from
dual;
NEXT_DAY(
---------
19-APR-06
32 NVL To replace a NULL value SQL> select empno, nvl(comm,0) from emp where
with a string. As in deptno = 30;
example NULL values are
replaced with 0. EMPNO NVL(COMM,0)
---------- -----------
7499 300
7521 500
7654 1400
7698 0
7844 0
7900 0
33 POWER It will return m raise to the SQL> select power(3,2) from dual;
power nth. 32
POWER(3,2)
----------
9
34 REPLACE It will replace a string SQL> select replace('AB D',' ','C') from dual;
within a string.
REPL
----
www.erpstuff.com admin@erpstuff.com 4/7
ABCD
SQL> select replace('ABC','B','E') from dual;
REP
---
AEC
35 ROUND (number) This function is used to SQL> select round(25.529,2) from dual;
round a number integer
places on right side of ROUND(25.529,2)
decimal point. If no integer ---------------
is specified then default is 25.53
rounding to 0 places.
SQL> select round(25.529) from dual;
ROUND(25.529)
-------------
26
36 ROUND (date) Rounding of date without SQL> select round(sysdate) from dual;
format it will be rounded to
nearest day. ROUND(SYSD
----------
15/04/2006
SQL> select round(sysdate, 'YEAR') from dual;
ROUND(SYSD
----------
01/01/2006
SQL> select round(sysdate, 'MONTH') from dual;
ROUND(SYSD
----------
01/04/2006
37 RPAD It will right pad the SQL> select rpad('Islamabad', 12, '*') from dual;
specified character within
the length specified. It is RPAD('ISLAMA
reverse of LPAD described ------------
above. Islamabad***
SQL> select rpad('123456', 12, '0') from dual;
RPAD('123456
------------
123456000000
38 RTRIM It will trim the specified SQL> select rtrim('USA', 'SA') from dual;
character(s) from the right
side of string. Without R
specified of any characters -
it will remove spaces from U
the right if any.
SQL> select length(rtrim('abc ')) from dual;
LENGTH(RTRIM('ABC'))
--------------------
3
39 SOUNDEX A very good function to SQL> create table t(name varchar2(20));
find spelling differences. As
in the example I have Table created.
spelled name by differing
www.erpstuff.com admin@erpstuff.com 5/7
‘e’ and ‘a’ so both names SQL> insert into t values ('Sikandar');
are different and phonetic
are same. 1 row created.
SQL> insert into t values ('Sikander');
1 row created.
SQL> select * from t;
NAME
--------------------
Sikandar
Sikander
SQL> select * from t where soundex(name) =
soundex('Sikandar');
NAME
--------------------
Sikandar
Sikander
SQL>
40 SQRT To get square root of a SQL> select sqrt(49) from dual;
number.
SQRT(49)
----------
7
41 SUBSTR A string function called as SQL> select substr('www.erpstuff.com',5,8) from
substring, to get a portion dual;
of string from the position
you specify up to length SUBSTR('
provided. As in example --------
www.erpstuff.com is a erpstuff
string then 5 is starting
position and 8 is number SQL> select substr('www.erpstuff.com',-12,8) from
of characters. To search dual;
from right to left you will
have to specify “-“. SUBSTR('
--------
erpstuff
42 SUM It is used to get sum of SQL> select sum(sal) from emp;
values.
SUM(SAL)
----------
29025
43 SYSDATE It will return current SQL> select sysdate from dual;
system date and time.
SYSDATE
---------
18-APR-06
44 TO_CHAR (character) To convert NCHAR, SQL> select to_char('01110') from dual;
NVARCHAR2, CLOB OR
NCLOB data to the TO_CH
database character set -----
01110
45 TO_CHAR (datetime) You can use this function SQL> select to_char('15/04/2006') from dual;
to covert date of (DATE,
TIMESTAMP, TIMESTAMP TO_CHAR('1
WITH TIME ZONE OR ----------
www.erpstuff.com admin@erpstuff.com 6/7
TIMESTAMP WITH LOCAL 15/04/2006
TIME ZONE) data types to
VARCHAR2. SQL> select to_char(sysdate, 'dd/mm/yyyy HH:MI')
from dual;
TO_CHAR(SYSDATE,
----------------
14/04/2006 03:24
SQL> select to_char(sysdate, 'dd/mm/yyyy HH24:MI')
from dual;
TO_CHAR(SYSDATE,
----------------
14/04/2006 15:25
46 TO_CHAR (number) To convert a NUMBER data SQL> select to_char(25) from dual;
type to VARCHAR2 data
type. TO
--
25
47 TO_CLOB
48 TO_DATE To convert a date text SQL> select to_date('15/04/2006', 'dd/mm/yyyy')
string to date. You may from dual;
also format the output.
TO_DATE('1
----------
15/04/2006
49 TO_NUMBER This function is useful to SQL> select to_number('0123') from dual;
convert a numeric string to
NUMBER. TO_NUMBER('0123')
-----------------
123
50 TRIM To remove leading or SQL> select trim(0 from 0000001230000000) from
trailing characters from a dual;
string.
TRI
---
123
51 UPPER To change the case from SQL> select upper('islamabad') from dual;
lower to upper.
UPPER('IS
---------
ISLAMABAD
52 USER It will return the current SQL> select user from dual;
session logged in user.
USER
------------------------------
SCOTT
www.erpstuff.com admin@erpstuff.com 7/7
