Aggregate function that act on a set of values.

These function are classified corresponding to different data types as:

1.String Function

2.Numeric Function

3.Conversion Function

4.Date Function.

Generally people know about common function like AVG, MIN, MAX, COUNT, SUM, POWER, ABS, SQRT.

Here i have listed some of very rare used function but to much useful.

1.INITCAP(char)

Return string with the first letter in upper case.

Example::SELECT INITCAP(”PUSHKAR RATHOD”) “Lower Case” FROM dual;

Lower Case
______________________
pushkar rathod

2.UPPER

This function works as same as above function except it convert string into UPPER case.

3.SUBSTR(char,m[,n])
Return a portion of char,beginning at character ‘m’,exceeding upto ‘n’ characters.If ‘n’
is ommitted,result returned upto end char.

Example::SELECT SUBSTR(’PUSHKAR’,3,4) “Substring” FROM dual;

Substring
_________________________
SHKA

4.LTRIM(char[,set])
This function removes char from left of char with initial characters removed up to the first
character not in set.

Example::SELECT LTRIM(’PUSHKAR’,S) “From Left” FROM dual;

From Left
_________________________
PUHKAR

5.RTRIM(char,[set])

This is same function as above.

6.LPAD(char1,n[,char2])

Returns ‘char1′ , left padded to length ‘n’ with the Sequence of characters in ‘char2′,’char2′ defaults to blanks.

Example::SELECT LPAD(’RATHOD P’,10,’#') “Left Pad’ FROM dual;

Left Pad
____________________________
##RATHOD P

7.RPAD(char1,n[,char2])

This is same function as above.