Number Functions

Ø  These functions accept numeric input and return numeric values as output.
Ø  Many functions return values that are accurate to 38 decimal point.
Round Function:
Syntax: Round(n,m)
Ø  It returns ‘n’ rounded to ‘m’ places right of the decimal point.
Ø  If ‘m’ is omitted, ‘n’ is round to 0 places.
Ø  ‘m’ can be negative and rounds off the digits to the left of the decimal point.
Ø  ‘m’ must be an integer.
Ex:
  • Select 15.293 num1, round(15.193,1) rounded from dual;
  • Select 15.193 num1, round(15.193, -1) rounded from dual;
  • Select 45.923 num1, round(45.923,2) rounded,round(45.923,0) rounded , round(45.923,-1) rounded from dual;
Truncate Function:
Syntax: Trunc(n,m)
Ø  It returns ‘n’ truncated to ‘m’ decimal places.
Ø  If ‘m’ is omitted, ‘n’ is truncated to 0 decimal places.
Ø  ‘n’ can be negative to truncate ‘m’ digits left of the decimal point.
Ex:
  • Select 15.79 num1, trunc(15.79,1) truncated from dual;
  • Select 15.79 num1, trunc(15.79,-1) truncated from dual;
  • Select 15.79 num1, trunc(45.923,2) truncated, trunc(42.923) truncated, trunk(45.923,-1) truncated from dual;


Ceil Function:
Syntax: Ceil(n)
Ø  Returns the smallest integer greater than or equal to ‘n’.
Ø  The adjustment is done to the highest nearest decimal value.
Ex:
  • Select 15.7 num1,ceil(15.7) ceiled from dual;
  • Select 14.27 num1, ceil(14.27) ceiled, ceil(14.2) ceiled, ceil(14) ceiled, ceil(0.22) ceiled from dual;
Floor Function:
Syntax: Floor(n)
Ø  Returns the largest integer less than or equal than ‘n’.
Ø  The adjustment is done to the lowest nearest decimal values.
Ex:
  • Select 15.7 num1, floor(15.7) floor from dual;
  • Select 14.27 num1, floor(14.27) floor, floor(14.2) floor, floor(14) floor, floor(0.002) floor from dual;
Modulus Function:
Syntax: MOD(m,n)
Ø  It returns remainder of ‘m’ divided by ‘n’.
Ø  It returns ‘m’ if ‘n’ is 0.
Ex:
  • Select mod(11,4), mod(10,2) from dual;
Power Function:
Syntax: power(m,n)
Ø  Returns ‘m’ raised to the ‘n’th power.
Ø  The base ‘m’ and the exponent ‘n’ can be any numbers.
Ø  If ‘m’ is negative, ‘n’ must be an integer.
Ex:
  • Select power(3,2), power(-3,2) from dual;
  • Select power(3,-2), power(-3,-2) from dual;
  • Select power(-3.5,-2), power(3,-2.5) from dual;
  • Select power(-3.5,2.5), power(3.5,-2.5) from dual;
Square Root Function:
Syntax: Sqrt(n)
Ø  It returns square root of ‘n’ as real value.
Ø  The value of ‘n’ cannot be negative.
Ex:
  • Select sqrt(25) from dual;
Absolute Function:
Syntax: ABS(n)
Ø  It returns the absolute value of ‘n’.
Ex:
  • Select abs(-15) from dual;
  • Select sal, comm, sal-comm, abs(sal-comm) from emp where comm = 1400;
Sign function:
Syntax: sign(n)
Ø  It returns the sign specification of a number.
Ø  If n < 0, Returns -1
Ø  If n = 0, Returns 0
Ø  If n < 0, Returns 1

Ex:

  • Select sign(-15), sign(15), sign(0) from dual;
  • Select sal, comm, sign(sal-comm) from emp where sign(sal-comm) = -1;