ORACLE FUNCTIONS

ORACLE FUNCTIONS

Functions are used to manipulate data items and return a result. Functions follow the format of:

Examples :-

Ø  AVG

·         Select AVG(s_price)”Average” from  data;

Ø  MIN

·         Select min(s_date)”Minimum Date” from data;

Ø  COUNT(expr)

·         Select count(s_order_no)”No of Orders”  from data;

Ø  COUNT(*)

·         Select count(*) “Total ” from data;

Ø  MAX

·         Select max(s_qty)”Maximum” from data;

Ø  SUM

·         Select sum(s_qty)”Total Qty.” from data;

Ø  ABS

·         Select ABS(-20)”Absolute” from dual;

Ø  POWER

·         Select power(3,2)”Raised” from dual;

Ø  ROUND

·         Select round(20.254,2)”Round” from dual;

Ø  SQRT

·         Select sqrt(49) “Square Root” from dual;

Ø  LOWER

·         Select lower(‘ABCD’)”LOWER” from dual;

Ø  INITCAP

·         Select inicap(‘PRADEEP SHARMA’)”Titel Case” from dual;

Ø  UPPER

·         Select uppar(‘rajeev gupta’) from dual;

Ø  SUBSTR

·         Select substr(‘123456789,1,3)”substruig” from dual;

Ø  LENGTH

·         Select length(‘apple’)”Length” from dual;

Ø  LTRIM

·         Select ltrim(‘xxxXxxWorld’,’x’)”Left trim example” from dual;

Ø  RTRIM

·         Select rtrim(‘wordxxXxx’,’x’)”reight trim example” from dual;

Ø  LPAD

·         Select lpad(‘ABC’,10,’*’)”LPAD” from dual;

Ø  RPAD

·         Select rpad(name,10,’x’)”Rpad example”  from data;

Ø  TO_NUMBER

·         Update data set amt=amt+to_number(sybstr(100,1,2);

The above oracle function are just a few selected from the many functions that are in built into oracle.