Numeric functions
Single value function
Significance of NULL value
Any arithmetic expression that includes null will result in null. i.e. If we add 10 to null the result will be null. If we multiply 10 to null it will again result in null.
Abs()
absolute value
Select abs(10),abs(-10) from dual;
10 10
Ceil()
Helps us to find the smallest integer (or whole number) that is greater then or equal to the specified value.
Select ceil(2),ceil(1.3) , ceil(-2), ceil(-2.3), ceil(10.2) from dual;
2 2 -2 -2 11
Floor()
Helps us to find the largest integer (or whole number) that is less then or equal to the specified value.
Select floor(2),floor(1.3) , floor(-2), floor(-2.3), floor(10.2) from dual;
2 1 -2 -3 10
Mod()
Helps us to find the reminder.
Syntax:
Mod(value,divisor)
Example:
Select mod(10,2),mod(20,6),mod(20,8) from dual;
0 2 4
Power()
Helps us to find the value x to the power y
Syntax:
Power(value,exponent)
Example:
Select power(2,3),power(3,2) from dual;
8 9
Sqrt()
Helps us to find squre root of the number.
Sqrt(number)
Select sqrt(16),sqrt(64) from dual;
4 8
Note: square root of –ve number is imaginary number , oracle does not support imaginary number so will produce an error.
Round()
Helps us to round off the value up to specific number of digits.
Select round(66.66666,2), round(66.66666,3), round(66.66666,4) from dual;
66.67 66.667 66.6667
Trunc()
Helps us to truncate the value after specific number of digits.
Select trunc(66.66666,2), trunc(66.66666,3), trunc(66.66666,4) from dual;
66.66 66.666 66.6666
Sign()
Helps us to find whether the number is +ve , -ve or zero
If +ve returns 1
If –ve returns -1
If zero returns 0
Select sign(10),sign(-10),sign(0) from dual;
1 -1 0
