Numeric functions in Oracle SQL
3shTech (trainings@trishtechnology.com)
Numeric Functions
Numeric functions accept numeric input and
return numeric values.
NUMERIC FUNCTIONS:-
· ABS
· SIGN
· SQRT
· MOD
· NVL
· POWER
· EXP
· LN
· LOG
· CEIL
· FLOOR
· ROUND
· TRUNC
· BITAND
· GREATEST
· LEAST
· COALESCE
1) ABS:
· Absolute value is the measure of the magnitude
of value.
· Absolute value is always a positive number.
Syntax: – ABS (value)
Example:–
SQL>SELECT ABS (5), ABS (-5), ABS (0), ABS (null) FROM DUAL;
· Sign gives the sign of a value.
Syntax: – SIGN (value)
Example:–
SQL>SELECT SIGN (5), SIGN (-5), SIGN (0), SIGN (null), SIGN (0.5) FROM DUAL;
· This will give the square root of the given
value.
· Input value must be positive.
Syntax: – SQRT (value)
Example:-
SQL>SELECT SQRT (2), SQRT (4), SQRT (9), SQRT (0), SQRT (null) FROM DUAL;
In case, if we enter a negative number then system will raise an
error.
SQL>SELECT SQRT (-2) FROM DUAL;
Output :
ORA-01428: argument ‘-2’ is out of
range
· This will give the remainder.
Syntax: – MOD (value, divisor)
Example:–
SQL>SELECT MOD (1,5),MOD (5,1),MOD (0,0),MOD (null, null),MOD (7,4),MOD (4,7),MOD (-4,7) FROM DUAL;
· NVL replaces the NULL value with the specified
value.
· Nvl function takes two input parameters.
I.e. If first parameter is null then it will return the second
parameter otherwise it will return the first parameter.
Syntax: – NVL (,)
Example:–
SQL>SELECT NVL (null, 5), NVL (100, 10), NVL (null, null) FROM DUAL;
In emp table for Emplyee ‘SMITH’, “comm” value is null…
SQL>SELECT empno, ename, comm, deptno FROM emp WHERE empno=7369;
Let’s use NVL function to convert the null value to known value
for “comm” column in EMP table.
SQL>SELECT empno, ename, nvl (comm, 100) emp_comm, deptno FROM emp WHERE empno=7369;
· POWER function is used to raise a value to a
given exponent.
Syntax: – POWER (value, exponent)
Example:–
SQL>SELECT POWER (2,3),POWER (3,2),POWER (1,1),POWER (0,0),POWER (null, null),POWER (-2,-3) FROM DUAL;
· This function will raise the value to the
given POWER.
Syntax: – EXP (value)
Example:–
SQL>SELECT EXP (0), EXP (1), EXP (2), EXP (null), EXP (-2) FROM DUAL;
· This function is based on natural or base e
logarithm.
· Input must be greater than zero.
Syntax: – LN (value)
Example:–
SQL>SELECT LN (1), LN (2), LN (null) FROM DUAL;
Suppose we try to enter a value that is zero or negative number
then system will raise an error.
SELECT LN (1), LN (2), LN (0), LN (null) FROM DUAL;
Output :
ORA-01428: argument ‘0’ is out of
range.
LN and EXP are reciprocal to each other.
EXP (3) = 20.0855369
· This function is based on 10 based logarithms.
· Input value must be greater than zero which is
positive only.
Syntax: – LOG (10, value)
Example:–
SQL>SELECT LOG (10, 1), LOG (10, 10), LOG (10, null) FROM DUAL;
SQL>SELECT LN (3), LOG (EXP (1), 3) FROM DUAL;
· This function will produce a whole number that
is greater than or equal to the specified value.
Syntax: – CEIL (value)
Example:–
SQL>SELECT CEIL(5.1),CEIL(5),CEIL(-5),CEIL(-5.1),CEIL(0),CEIL(null) FROM DUAL;
· This function will produce a whole number that
is less than or equal to the specified value.
Syntax: – FLOOR (value)
Example:–
SQL>SELECT FLOOR(5.1),FLOOR(5),FLOOR(-5),FLOOR(-5.1),FLOOR(0),FLOOR(null) FROM DUAL;
· This function will ROUND numbers to a given
number of digits of precision.
Syntax: – ROUND (value, precision)
Example:–
SQL>SELECT ROUND (123.2345), ROUND (123.2345, 2), ROUND (123.2354, 2) FROM DUAL;
SQL>SELECT ROUND (123.2345, 0), ROUND (123.2345,-2), ROUND (123.2354,-4) FROM DUAL;
· This function will truncates or chops off
digits of precision from a number.
Syntax: – TRUNC (value, precision)
Example:–
SQL>SELECT TRUNC (123.2345), TRUNC (123.2345, 2), TRUNC (123.2354, 3) FROM DUAL;
· This function will perform bitwise and
operation.
Syntax: – BITAND (value1, value2)
Example:–
SQL>SELECT BITAND (2,3),BITAND (1,1),BITAND (0,0),BITAND (0,1),BITAND (null, null),BITAND (-1,-2) FROM DUAL;
· This function will give the greatest number in
list of values.
· It will take ‘n’ number of arguments but data
type should be same.
Syntax: – GREATEST (value1, value2….valuen)
Example:–
SQL>SELECT GREATEST (1, 4, null, 0,-5) FROM DUAL;
SQL>SELECT GREATEST (1, 4, 1000, 1.005,-5) FROM DUAL;
· If all the values are zeros then it will
display zero.
· If all the parameters are nulls then it will
display nothing.
· If any of the parameters is null it will
display nothing.
· This function will give the least number in
list of values.
· It will take ‘n’ number of arguments but data
type should be same.
Syntax: – LEAST (value1, value2….valuen)
Example:–
SQL>SELECT LEAST (1, 2, 3), LEAST (-1, -2, -3) FROM DUAL;
· If all the values are zeros then it will
display zero.
· If all the parameters are nulls then it will
display nothing.
· If any of the parameters is null it will
display nothing.
· This function will return first non-null value
in the specified list.
Syntax: – COALESCE (value1, value2….valuen)
Example:–
SQL>SELECT COALESCE (null, 2, null, 1) FROM DUAL;
Happy Learning 😊
Thank you!!
Comments
Post a Comment