FUNCTIONS

5 TYPES OF FUNCTION

1. CHARACTER FUNCTIONS

CHARACTER functions accept character input and return character or number values.

these are of 2 types.

a)  CASE MANIPULATION

a1)   LOWER

for eg.

SELECT LOWER('ABC') FROM DUAL;

OUTPUT-abc

a2) UPPER

for eg.

SELECT UPPER('abc') FROM DUAL;

OUTPUT-ABC

a3) INITCAP

for eg.

SELECT INITCAP('abc') FROM DUAL;

OUTPUT-Abc

b) CHARACTER MANIPULATION

b1) CONCAT

for eg.

SELECT CONCAT('abc','xyz') FROM DUAL;

OUTPUT-abcxyz

b2) SUBSTR

 for eg.

SELECT SUBSTR('abcxyz',4,2) FROM DUAL;

OUTPUT-xy

b3) LENGTH

for eg.

SELECT LENGTH('ABCD') FROM DUAL;

OUTPUT-4

b4) INSTR

for eg.

SELECT INSTR('ABC','C') FROM DUAL;

OUTPUT-3

b5) LPAP

for eg.

SELECT LPAP('abc',5,'x') FROM DUAL;

OUTPUT-xxabc

b6) RPAP

for eg.

SELECT RPAP('abc',5,'x') FROM DUAL;

OUTPUT-abcxx

b7) TRIM

for eg.

SELECT TRIM('a' FROM 'abc') FROM DUAL;

OUTPUT-bc    

2. NUMBER FUNCTIONS   

Number functions accept numeric input and return numeric values. 

a)  ABS(N)

 This function is used to return the absolute value of a numeric input argument.

for eg.

SELECT ABS(-15) FROM DUAL;

OUTPUT- -15

b) CEIL(N)

This function returns next smallest integer greater than or equal to parameter passed N.

for eg.

SELECT CEIL(15.5) FROM DUAL;

OUTPUT-16

c) FLOOR(N) 

for eg.

SELECT FLOOR(15.5) FROM DUAL;

OUTPUT-15

d) MOD(M,N)

for eg.

SELECT MOD(15,3) FROM DUAL;

OUTPUT-0

e) POWER(M,N) 

for eg.

SELECT POWER (2,2) FROM DUAL;

OUTPUT-4

f) ROUND(M,N)

for eg.

SELECT ROUND(45.289,2) FROM DUAL;

OUTPUT-45.29

g) SQRT(N)

for eg.

SELECT SQRT(4) FROM DUAL;

OUTPUT-2

f) TRUNC(M,N)

for eg.

SELECT TRUNC(46.628,-1) FROM DUAL;

OUTPUT-40

3. DATE CONVERSION FUNCTIONS

Date functions operate on values of daat type and way return either a number or day.

a1) SYSDATE

it returns current date of system.

SELECT SYSDATE FROM DUAL;

OUTPUT- 23-JUN-13

a2) CURRENT DATE

it returns current date of the year.

SELECT CURRENT DATE FROM DUAL;

OUTPUT- 23-JUN-13

INBUILT DATE FUNCTIONS

b1) MONTHS_BETWEEN(;'DATE','DATE') 

for eg.

SELECT MONTHS_BETWEEN('01-jan-14','01-jan-13') FROM DUAL;

OUTPUT-12

b2) ADD_MONTHS('DATE',NO. OF MONTHS)

for eg.

SELECT ADD_MONTHS('20-JUN-13',3) FROM DUAL;

OUTPUT-  20-SEP-13

b3) NEXT_DAY('DATE',NAME OF DAY)

for eg.

SELECT NEXT_DAY('20-JUN-13',THURSDAY) FROM DUAL;

OUTPUT-   227-JUN-2013

b4) LAST_DAY('DATE')

for eg.

SELECT LAST _DAY('23-JUN-13') FROM DUAL;

b5)   1.  ROUND('DATE',MONTH)

for eg.

SELECT ROUND('23-JUN-13) FROM DUAL;

OUTPUT-30-JUN-13

        2.   ROUND('DATE',YEAR)

for eg.

SELECT ROUND('23-JUN-13) FROM DUAL;

OUTPUT-   1-JAN-13

b6) 1. TRUNC('DATE',MONTH)

for eg.

SELECT TRUNC('07-SEP-13',MONTH) FROM DUAL;

OUTPUT-  01-SEP-13

      2. TRUNC('DATE',YEAR)

for eg.

SELECT TRUNC('07-SEP-13',YEAR) FROM DUAL;

OUTPUT- 01-JAN-13

 4. CONVERSION FUNCTIONS

Conversion functions are used to convert value from one data type to another data type.

these are of 2 types.

1.IMPLICIT

This conversion takes place automatically.for eg. int to double

2.EXPLICIT

This conversion is done forcefully by the user.for eg.int to char.

a) TO_CHAR(DATE CONVERSION)

This function is basically used for transforming a date value into a varchar2 value.

syntax: TO_CHAR(DATE VALUE[,DATE_FORMAT])

b) TO_CHAR(NUMERIC CONVERSION)

This function is used for converting numeric value into a varchar2 value.

syntax: TO_CHAR(N[,NUM_FORMAT])

c) TO_DATE( )

This function is used to convert a character string into its date.

syntax: TO_DATE(CHAR_VALUE[,FORMAT])

d) TO_NUMBER( )

This function is used to convert a char value into numeric value

syntax: TO_NUMBER('CHAR_VALUE')

5. GENERAL FUNCTIONS

General functions are like NULL,CASE,DECODE etc.

a) DECODE

its an alternative of if else

syntax: DECODE(base value ,e value_value1,return value1[,e value_value2,return value2],...................)

b) LEAST 

This function is used to return smallest value in list of values given.

syntax: LEAST(VALUE1,VALUE2..................................)

for eg.

SELECT LEAST(1,2,0) FROM DUAL;

OUTPUT-O

SELECT LEAST('AMIT','RAM') FROM DUAL;

OUTPUT-AMIT

c) GREATEST

This function is used to return greatest value in list of given values.

syntax: GREATEST(VALUE1,VALUE2..................)

for eg.

SELECT GREATEST(1,0,2) FROM DUAL;

OUTPUT-2

d) NVL( )

This function is used for evaluating an expression and returning  a given value if the expression evaluates to NULL

syntax: NVL(EXPRESSION,RETURN_VALUE)

e) NVL2( )

syntax: NVL2(EXPRESSION,VALUE_IF_NOT_NULL,VALUE_IF_NULL)

f) UID

This function returns number that usually identifies user.

syntax: SELECT UID FROM DUAL;

g) USER

This function returns name of currently connected user.

syntax: SELECT USER FROM DUAL;