SQL Functions

SQL functions are pre-defined program to perform specific tasks. These functions can receive values from outside in the form of Parameters and they return one value. SQL groups functions into two major categories:

1) Group Functions:

Those functions that process multiple records to give single record output. Some of the main group functions are:

a) SUM ()
b) AVG ()
c) MAX ()
d) MIN ()
e) COUNT ( * )

2) Row Functions:

Those functions that work on each record and give output for each record. Row functions are further categories into five groups:

a) Number Functions:
Input values are number and return value is number.

b) Character Functions:
Input values or Return value, any one is character.

c) Date Functions:
Input value or Return value, any one is Date.
d) Conversion Functions
Changes data type of Input value without changing the value.
e) Other Functions:
Input values or their data types or return value type are not fixed.

Restriction on Group Function:

· Group Functions are allowed only with SELECT & HAVING clauses
· Group Function can not be combined with Row Functions or Individual Columns
· Group Functions does not consider NULL value records

GROUP BY Clause:

This clause is used to group related records based on a column or set of columns. Group By clause is very useful for Group Functions as it allows the use of Row Functions and Individual Columns with Group Functions.

Syntax:

GROUP BY

HAVING Clause:

This clause is used to put conditions on the group of records. HAVING clause can not be used without GROUP BY clause as groups of records are provided by it. HAVING conditions are same as WHERE clause conditions but it allows the use of group functions directly.

Number Functions:

a) ABS ((n))
Returns absolute (positive) value.

b) SIGN ((n))
Returns 1 if value is positive, -1 if value is negative and 0 if value is zero.

c) POWER ((n),(p))
Returns value raised up to times.

d) MOD ((n),(d))
Returns remainder of division. is divided by .

e) ROUND ((n) [,(d>))
Returns rounded off value up to specified number of decimal digits. If is not defined then no decimal digit.

f) TRUNC ((n), [,(d)])
Same as ROUND function but does not apply rounding off logic when truncating decimal digits.

g) CEIL ((n))
Returns smallest integer but greater than or equal to value.

h) FLOOR ((n))
Returns greatest integer but less than or equal to value

Character Functions:


a) SUBSTR ((string), (start) [,(no.)])
Returns (no.) from (string) starting from (start).

b) LENGTH ((string))
Returns count of characters within the (string).

c) INSTR ((string-1), (string-2))
Returns position of (string-2) within (string-1), if string-2 is not found then zero.

d) UPPER ((string))
Returns the string with alphabets converted into capital letters.

e) LOWER ((string))
Returns the string with alphabets converted into small letters.

f) LTRIM ((string) [,(char)])
Returns the string removing the from the left side of the string. If (charset) is not given then removes all leading spaces.

g) RTRIM ((string) [,(char)])
Returns the string removing the from the right side of the string. If (char) is not given then removes all trailing spaces.

h) LPAD ((string), (output) [,(filler)])
Returns the string within the

. If string is smaller than the size then (filler) is used to fill the left blank spaces. Default filler is space.

i) RPAD ((string), (output) [,(filler)])
Returns the string within the

. If string is smaller than the size then (filler) is used to fill the right blank spaces. Default filler is space.

j) INITCAP ((string))
Returns the string with each word starting with upper case letter and rest in lower.

k) ASCII ((char))
Returns the ASCII value of character.

l) CHR ((ascii))
Returns the character equivalent for given ascii value.

m) REPLACE ((string), (pattern) [,(replace)])
Returns the string replacing the (pattern) with (replace). If replace string is not defined then (pattern) is removed from the string.

n) TRANSLATE ((string), (char), (translate)
)Returns the string translating the (char) characters with character from (translate).

Date Functions:

a) ADD_MONTHS ((date), (no.))
Adds number of months to the date and return a new date. If no. of months is negative then it subtracts the months from date.

b) MONTHS_BETWEEN ((date-1), (date-2))
Returns difference of number of months between both the dates.

c) SYSDATE
Returns the system date.

d) LAST_DAY ((date))
Returns a new date with the last day of the month.

e) NEXT_DAY ((date), (day))
Returns a date for the next day from the date mentioned.

Conversion Functions:

a) TO_CHAR ((date/number) [,(output)])

It converts number or date values in character. Output format is used to change the date format or number formats.

b) TO_DATE((chars/numbers) [,(input format)])
It converts character or number into date. Output format is used to receive the date or number in different format and to convert them into default date format of DD-MON-YY.

c) TO_NUMBER ((char))
It converts a character value containing valid numeric value into number value.

Other Functions:

a) NVL ((value), (return))
This function is to check for NULL and to return a value instead of NULL value. Value and Return Value both must be of same data type.

b) GREATEST ((value))
Returns the largest value from the list of values.

c) LEAST ((value))
Returns the smallest value from the list of values.

d) USER
Returns the current user name.

DECODE DECODE (value) {,(pattern) , (value)} […] [,(default value)])
This function is an additional feature from Oracle to SQL. It is used to check pattern with value and returns a value for each pattern. If none of the pattern matches with the value then a default value can be defined that is optional.