Oracle BUILT-IN FUNCTIONS

ORACLE BUILT-IN FUNCTIONS

Command: abs()

select abs(-40) from dual;

ABS(-40)

40

Command: floor()

 select floor(56.99) from dual;

FLOOR(56.99)

56

Command: ceil()

 select ceil(56.01) from dual;

CEIL(56.01)

57

Command: power()

 select power(5,2) from dual;

POWER(5,2)

25

Command: sqrt()

 select sqrt(25) from dual;

SQRT(25)

5

Command: mod()

 select mod(10,7) from dual;

MOD(10,7)

3

Command: cos()

 select cos(0) from dual;

COS(0)

1

Command: sin()

 select sin(0) from dual;

SIN(0)

0

Command: tan()

 select tan(10) from dual;

TAN(10)

.648360827

Command: exp()

 select exp(0) from dual;

EXP(0)

1

Command: trunc()

select trunc(4.11509,2) from dual;

TRUNC(4.11509,2)

4.11

Command: round()

 select round(4.11509,2) from dual;

ROUND(4.11509,2)

4.12

CHARACTER FUNCTION

Command: replace()

select replace('Ram_Babu.S','Babu','Kumar')from dual;

REPLACE('RA

Ram_Kumar.S

Command: chr()

 select chr(65) from dual;

C

A

Command: lower()

 select lower('A') from dual;

L

a

Command: upper()

 select upper('a') from dual;

U

A

Command: lpad()

 select lpad('abcde',2) from dual;

LP

ab

Command: ltrim()

 select ltrim('abcde','ab') from dual;

LTR

cde

Command: rtrim()

 select rtrim('abcde','de') from dual;

RTR

abc

Command: concat()

 select concat('Ram','Babu') from dual;

CONCAT(

RamBabu

Command: ascii()

 select ascii('A') from dual;

ASCII('A')

65

Command: sign()

 select sign(-75)from dual;

SIGN(-75)

-1

Command: substr()

 select substr('Ram_Babu.S',5,4)from dual;

SUBS

Babu

Command: instr()

 select instr('abcabcabcabc','ab',5,2)from dual;

INSTR('ABCABCABCABC','AB',5,2)

10

Command: length()

 select length('Ram_Babu.S')from dual;

LENGTH('RAM_BABU.S')

10

Command: greatest()

 select greatest(-56,0.38,49)from dual;

GREATEST(-56,0.38,49)

49

select greatest('Ram','babu','Vignesh')from dual;

GREA

babu

Command: translate()

 select translate('Ram_Babu.S','Babu','Kumar')from dual;

TRANSLATE(

Rum_Kuma.S

DATE FUNCTION

Command: months_between()

select months_between('04-jan-2004','04-sep-1993')from dual;

MONTHS_BETWEEN('04-JAN-2004','04-SEP-1993')

124

Command: last_day()

select last_day('05-JAN-2004')from dual;

LAST_DAY(

31-JAN-04

Command: add_months()

select add_months('04-JAN-2012',3) from dual;

ADD_MONTH

04-APR-12

Command: next_day()

select next_day('21-JAN-2012','sat')from dual;

NEXT_DAY(

28-JAN-12

Command: greatest()

select greatest('05-FEB-1973','12-OCT-1983','24-DEC-1993') from dual;

GREATEST('0

24-DEC-1993

Command: least()

select least('05-FEB-1973','12-OCT-1983','24-DEC-1993') from dual;

LEAST('05-F

05-FEB-1973

CONVERSION FUNCTION

Command: to_char()

select to_char(sysdate,'ddth "of" Month') from dual;

TO_CHAR(SYSDATE,'

21st of January

select to_char(sysdate,'mm.dd.yyyy') from dual;

TO_CHAR(SY

01.21.2012

select to_char(sysdate,'dd/mm/yy hh:mi:ss') from dual;

TO_CHAR(SYSDATE,'

21/01/12 03:23:56

Command: to_date()

select to_date('01-06-JAN','dd-yy-mm') from dual;

TO_DATE('

01-JAN-06

select to_date('05/2008','dd/yy') from dual;

TO_DATE('

05-JAN-08

Command: count()

select count(e_no) from employee;

COUNT(E_NAME)

3

Command: max()

select max(salary) from employee;

MAX(SALARY)

60000

Command: min()

select min(salary) from employee;

MIN(SALARY)

25000

Command: avg()

select avg(salary) from employee;

AVG(SALARY)

45000

Command: sum()

select sum(salary) from employee;

SUM(SALARY)

135000