Oracle SQL Functions

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