Oracle Numeric/Math Functions

Yusuf SEZER

5 years ago

This article contains information about oracle numeric/math functions and their usage.

Numeric/Math Functions

Oracle has several functions for processing numerical data.

ABS Function in Oracle

Returns the absolute value of the numerical value.

SELECT ABS(-15) AS RESULT FROM DUAL;
<em>Result:</em> 15

SELECT ABS(-15.24) AS RESULT FROM DUAL; 
<em>Result:</em> 15.24

SELECT ABS(-15) AS RESULT FROM DUAL;

<em>Result:</em> 15

SELECT ABS(-15.24) AS RESULT FROM DUAL;

<em>Result:</em> 15.24

ACOS Function in Oracle

Returns the arc cosine of the numerical value.

SELECT ACOS(0.5) AS RESULT FROM DUAL;
Result: 1.0471


SELECT ACOS(-0.25) AS RESULT FROM DUAL; 
Result: 1.8235

SELECT ACOS(0.5) AS RESULT FROM DUAL;

Result: 1.0471

SELECT ACOS(-0.25) AS RESULT FROM DUAL;

Result: 1.8235

ASIN Function in Oracle

Returns the arc sine value of the numerical value.

SELECT ASIN(0.5) AS RESULT FROM DUAL;
Result: 0.5236


SELECT ASIN(-0.3) AS RESULT FROM DUAL; 
Result: -0.3047

SELECT ASIN(0.5) AS RESULT FROM DUAL;

Result: 0.5236

SELECT ASIN(-0.3) AS RESULT FROM DUAL;

Result: -0.3047

ATAN Function in Oracle

Returns the arc tangent of the numerical value.

SELECT ATAN(0.2) AS RESULT FROM DUAL;
Result: 0.1974

1 2	SELECT ATAN(0.2) AS RESULT FROM DUAL; Result: 0.1974

ATAN2 Function in Oracle

Returns the arc tangent of two numerical value.

SELECT ATAN2(0.2,0.3) AS RESULT FROM DUAL; 
Result: 0.5880

1 2	SELECT ATAN2(0.2,0.3) AS RESULT FROM DUAL; Result: 0.5880

AVG Function in Oracle

Calculates and returns the avarage value of an expression.

select avg(salary) from salary;
select job, avg(salary) from salary group by job;

1 2	select avg(salary) from salary; select job, avg(salary) from salary group by job;

BITAND Function in Oracle

This function implements “With AND” operation to numerical values given as parameters and returns an integer.

SELECT BITAND(7, 2) AS RESULT FROM DUAL;
Result: 2

1 2	SELECT BITAND(7, 2) AS RESULT FROM DUAL; Result: 2

CEIL Function in Oracle

Rounds the numerical value to next bigger integer value.

SELECT CEIL(99.4) AS RESULT FROM DUAL;
Result: 100

SELECT CEIL(-99.4) AS RESULT FROM DUAL;
Result: -98

SELECT CEIL(99.4) AS RESULT FROM DUAL;

Result: 100

SELECT CEIL(-99.4) AS RESULT FROM DUAL;

Result: -98

COS Function in Oracle

Returns the cosine value of the numerical value.

SELECT COS(0.5) AS RESULT FROM DUAL;
Result: 0.87758256

1 2	SELECT COS(0.5) AS RESULT FROM DUAL; Result: 0.87758256

COSH Function in Oracle

Returns the hyperbolic cosine value of the numerical value.

SELECT COSH(0.5) AS RESULT FROM DUAL;
Result: 1.1276

1 2	SELECT COSH(0.5) AS RESULT FROM DUAL; Result: 1.1276

EXP Function in Oracle

Returns e raised to the nth power, where e = 2.71828183.

SELECT EXP(2) AS RESULT FROM DUAL;
Result: 7.3890

1 2	SELECT EXP(2) AS RESULT FROM DUAL; Result: 7.3890

FLOOR Function in Oracle

Rounds the numerical value to next smaller integer value.

SELECT FLOOR(99.4) AS RESULT FROM DUAL; 
Result: 98 

SELECT FLOOR(-99.4) AS RESULT FROM DUAL; 
Result: -100

SELECT FLOOR(99.4) AS RESULT FROM DUAL;

Result: 98

SELECT FLOOR(-99.4) AS RESULT FROM DUAL;

Result: -100

GREATEST Function in Oracle

Returns the largest value in expressions given as parameters.

SELECT GREATEST(1, 5, 0, -3) AS RESULT FROM DUAL;
Result: 5

1 2	SELECT GREATEST(1, 5, 0, -3) AS RESULT FROM DUAL; Result: 5

SELECT GREATEST('yusuf1', 'yusuf3', 'yusuf0') AS Result FROM DUAL;
Result: yusuf3

1 2	SELECT GREATEST('yusuf1', 'yusuf3', 'yusuf0') AS Result FROM DUAL; Result: yusuf3

LEAST Function in Oracle

Returns the smallest value in expressions given as parameters.

SELECT LEAST(1, 5, 0, -3) AS RESULT FROM DUAL;
Result: -3

1 2	SELECT LEAST(1, 5, 0, -3) AS RESULT FROM DUAL; Result: -3

SELECT LEAST('yusuf1', 'yusuf3', 'yusuf0') AS Result FROM DUAL;
Result: yusuf0

1 2	SELECT LEAST('yusuf1', 'yusuf3', 'yusuf0') AS Result FROM DUAL; Result: yusuf0

SELECT LEAST('yusuf2', 'yusuf13', 'yusuf3') AS Result FROM DUAL;
Result: yusuf13

1 2	SELECT LEAST('yusuf2', 'yusuf13', 'yusuf3') AS Result FROM DUAL; Result: yusuf13

LN Function in Oracle

Returns the natural logarithm of the numerical value.

SELECT LN(20) AS Result FROM DUAL;
Result: 2.9957

1 2	SELECT LN(20) AS Result FROM DUAL; Result: 2.9957

LOG Function in Oracle

Returns the logarithm of a base b.

SELECT LOG(a,b) AS Result FROM DUAL;

SELECT LOG(10, 100) AS Result FROM DUAL;
Result:2

SELECT LOG(a,b) AS Result FROM DUAL;

SELECT LOG(10, 100) AS Result FROM DUAL;

Result:2

MAX Function in Oracle

Returns the maximum value of an expression.

select MAX(salary) from salary;
select job, MAX(salary) from salary group by job;

1 2	select MAX(salary) from salary; select job, MAX(salary) from salary group by job;

MIN Function in Oracle

Returns the minimum value of an expression.

select MIN(salary) from salary;
select job, MIN(salary) from salary group by job;

1 2	select MIN(salary) from salary; select job, MIN(salary) from salary group by job;

MEDIAN Function in Oracle

Returns the median of an expression.

select MEDIAN(salary) from salary;

1	select MEDIAN(salary) from salary;

MOD Function in Oracle

Returns the remainder of a divided by b.

SELECT MOD(a,b) AS Result FROM DUAL; 

SELECT MOD(27, 4) AS Result FROM DUAL; 
Result:3

SELECT MOD(a,b) AS Result FROM DUAL;

SELECT MOD(27, 4) AS Result FROM DUAL;

Result:3

POWER Function in Oracle

Returns a raised to the bth power.

SELECT POWER(a,b) AS Result FROM DUAL; 

SELECT POWER(2, 3) AS Result FROM DUAL; 
Result:8

SELECT POWER(3, 3) AS Result FROM DUAL; 
Result:27

SELECT POWER(a,b) AS Result FROM DUAL;

SELECT POWER(2, 3) AS Result FROM DUAL;

Result:8

SELECT POWER(3, 3) AS Result FROM DUAL;

Result:27

ROUND Function in Oracle

The parameters of the function are as follows.

ROUND(number,decimal_places)

number -> number will be rounded

decimal_places -> This parameter is optinal. If you do not specify this parameter, functions removes ol decimal places from the number. If you set this parameter, it will round decimal places as much as this parameters value.

SELECT ROUND(12.3657) AS Result FROM DUAL;
Result: 12

SELECT ROUND(12.3657,2) AS Result FROM DUAL; 
Result:12.37

SELECT ROUND(12.312,2) AS Result FROM DUAL; 
Result:12.31

SELECT ROUND(12.3657) AS Result FROM DUAL;

Result: 12

SELECT ROUND(12.3657,2) AS Result FROM DUAL;

Result:12.37

SELECT ROUND(12.312,2) AS Result FROM DUAL;

Result:12.31

SIGN Function in Oracle

Returns the sign of the numerical value.

SELECT SIGN(-12) AS RESULT FROM DUAL;
Result: -1

SELECT SIGN(-0.123) AS RESULT FROM DUAL;
Result: -1

SELECT SIGN(1) AS RESULT FROM DUAL;
Result: 1

SELECT SIGN(0.1111) AS RESULT FROM DUAL;
Result: 1

SELECT SIGN(0) AS RESULT FROM DUAL;
Result: 0

SELECT SIGN(-12) AS RESULT FROM DUAL;

Result: -1

SELECT SIGN(-0.123) AS RESULT FROM DUAL;

Result: -1

SELECT SIGN(1) AS RESULT FROM DUAL;

Result: 1

SELECT SIGN(0.1111) AS RESULT FROM DUAL;

Result: 1

SELECT SIGN(0) AS RESULT FROM DUAL;

Result: 0

SIN Function in Oracle

Returns the sine of the numerical value.

SELECT SIN(5) AS RESULT FROM DUAL;
Result: -0.95892427

1 2	SELECT SIN(5) AS RESULT FROM DUAL; Result: -0.95892427

SINH Function in Oracle

Returns the hyperbolic sine of the numerical value.

SELECT SINH(5) AS RESULT FROM DUAL;
Result: 74.2032

1 2	SELECT SINH(5) AS RESULT FROM DUAL; Result: 74.2032

SQRT Function in Oracle

Returns the square root of the numerical value.

SELECT SQRT(25) AS RESULT FROM DUAL;
Result: 5

SELECT SQRT(5) AS RESULT FROM DUAL; 
Result: 2.2360

SELECT SQRT(25) AS RESULT FROM DUAL;

Result: 5

SELECT SQRT(5) AS RESULT FROM DUAL;

Result: 2.2360

SUM Function in Oracle

Returns the summed value of the numerical value.

select SUM(salary) from salary; 
select job, SUM(salary) from salary group by job;
select job, SUM(salary+bonus) from salary group by job;

select SUM(salary) from salary;

select job, SUM(salary) from salary group by job;

select job, SUM(salary+bonus) from salary group by job;

TAN Function in Oracle

Returns the tangent of the numerical value.

SELECT TAN(0.5) AS RESULT FROM DUAL;
Result: 0.54630249

1 2	SELECT TAN(0.5) AS RESULT FROM DUAL; Result: 0.54630249

TANH Function in Oracle

Returns the hyperbolic tangent of the numerical value.

SELECT TANH(0.5) AS RESULT FROM DUAL;
Result: 0.4621

1 2	SELECT TANH(0.5) AS RESULT FROM DUAL; Result: 0.4621

TRUNC Function in Oracle

This functions truncates the decimal places on a decimal value. If you specify second parameter which is optinal, it truncates the decimal places according to this parameter.

Examples:

SELECT TRUNC(35.234) AS RESULT FROM DUAL;
Result: 35

SELECT TRUNC(35.234, 0) AS RESULT FROM DUAL;
Result: 35

SELECT TRUNC(35.234, 1) AS RESULT FROM DUAL;
Result: 35.2

SELECT TRUNC(35.234, 2) AS RESULT FROM DUAL;
Result: 35.23

SELECT TRUNC(35.234, 3) AS RESULT FROM DUAL;
Result: 35.234

SELECT TRUNC(35.234) AS RESULT FROM DUAL;

Result: 35

SELECT TRUNC(35.234, 0) AS RESULT FROM DUAL;

Result: 35

SELECT TRUNC(35.234, 1) AS RESULT FROM DUAL;

Result: 35.2

SELECT TRUNC(35.234, 2) AS RESULT FROM DUAL;

Result: 35.23

SELECT TRUNC(35.234, 3) AS RESULT FROM DUAL;

Result: 35.234