Function in SQL

These are provided for retrieving the information from the tables in various scenarios. They are 2 types

1. Single Row Functions
2. Group function

Single Row Functions

These functions execute once for each row that is present in the table i.e. if the table has '0' row it execute 'zero' times or 'n' rows it execute 'n' times.

Group function

These functions take a Group of rows into consideration & returns a single value a single value as an O/P i.e. if the table contains 'zero' or 'n' row it executes once & returns an O/P.
A function can be used in 2 different ways

1. Columns list of a select statement  : like select name, len (ename) from Emp
2. Condition of a select statement : like select * from Emp where len (name) = 5

Syntax of calling function

Select <function name> ([argument list]) [from <Tname> <condition>]

Note
From is optional if the argument does not contain any column name in it.
Example

• Select len ('sharad')
• Select len (ename) from Emp

Single Row Functions

• Arithmetic functions
• String functions
• Date functions
• Conversion functions
• System function

Arithmetic  Functions

The following are the arithmetic function

ABS (n)

It returns the absolute value of the given n

Example:

1 select ABS (15)   Ans: 15
2 select ABS (-15) Ans: 15

Sign(n)

It returns 1 if the given n is > 0 (or)
It returns 0 (zero) if the given n is = 0 (or)
It returns -1 if the given n is < 0.

Example

select sign (100)       Ans: 1
select sign (0)           Ans: 0
select sign (-100)     Ans: -1

Ceiling (n) 

It returns the least integer that is greater than the given n.

Example

select Ceiling (15.3) Ans: 16
select Ceiling (-15.3) Ans: -15
 
Floor (n)

It returns the highest integer less than the given number n.

Example

select Floor (15.6) Ans: -15
select Floor (-15.6) Ans: -16

Round (n, size [,funValue]

It returns the given n value rounded with specified decimals as size.

Example

select Round (156.678, 2) Ans 156.68
select Round (156.678, 0) Ans 157
select Round (156.678,-1) Ans 160
select Round (156.678,-3) Ans 0

The size can be negative value also. If it is negative, the rounding is performed before the decimal.
 

1

5

6

6

7

8

The function value which is an optional parameter can be anything greater than '0' If it is used the function behaves as Truncate. So it will cut if the values but will not round them.

Example

select Round (156.678, 2, 1) Ans 156.67
select Round (156.678,-1, 1) Ans 150
select Round (156.678,-2, 1) Ans 100

PI ()
It returns the constant value of PI.

Example

select Pi () Ans: 3.14159263
select Round (Pi (), 2) Ans: 3.14

Power (n, m)

It returns the value of n power m.

Example

select Power (2,3) Ans: 8
select Power (3,4) Ans: 81

Square (n)

It returns the square of the given 'n'.

Example

select Square (2) Ans: 4

Sqrt (n)

It returns the square root of the given 'n'.

Example

select Sqrt (4) Ans: 2

Log (n)

It returns the natural logarithmic value of the given 'n' i.e. base 'e'

Example

select Log (10)
select Log10 (n)  Ans: It returns the logarithmic value of given ‘n’ to base 10.

Rand ([seed])

It returns a random no. ranging between 0.0 to 1 for each time it is executed.

Example

select Rand () Ans: 0.8077, 0.5377, etc.
select Rand () * 50 Ans: 42.172 ……….upto 50

If we want to generate a random no. between 1 and specified upper limit, multiply the function with the upper limit value and round it as following.

Example

select Rand () * 50
select Round (Rand () * 50, 0)
The optional parameter seed can be used for repeating the value one more time because for a given seed if always returns the same value. If the seed changes the value also changes.

NOTE
The Arithmetic functions provide with all the trigonometric functions like Sin, Cos, Tan, etc for which we need to provide the degrees as 'n' value.

In the next article we will discuss about string function.