Mathematical Functions and Operators
Mathematical Functions
The following table shows the mathematical functions that are available for the standard numeric types.
In the table below, the returned data type or value appears following the right arrow (⇒) sign.
| Function | Description and Comments | Example |
|---|---|---|
ABS(numeric_type) => numeric_type | Absolute | ABS(-88)=> 88 |
CEIL(DECIMAL) => DECIMAL CEIL(DOUBLE) => DOUBLE | Nearest integer greater than or equal to argument Alias: CEILING() | CEIL(35.78)=> 36 CEIL(-35.18)=> -35 |
DIV(y numeric_type, x numeric_type) => int_type | Integer quotient of y/x | DIV(18, 3)=> 6 DIV(18, 5)=> 3 |
EXP(DECIMAL) => DECIMAL EXP(DOUBLE) => DOUBLE | Exponential | EXP(1.0)=> 2.7182818284590452 |
FLOOR(DECIMAL) => DECIMAL FLOOR(DOUBLE) => DOUBLE | Nearest integer less than or equal to argument See notes. | FLOOR(56.8)=> 56 FLOOR(-56.8)=> -57 |
LN(DECIMAL) => DECIMAL LN(DOUBLE) => DOUBLE | Natural logarithm | LN(2.0)=> 0.6931471805599453 |
LOG(DECIMAL) => DECIMAL LOG(DOUBLE) => DOUBLE | Base 10 logarithm | LOG(1000)=> 3 |
LOG(b DECIMAL, x DECIMAL) => DECIMAL | Logarithm of x to base b | LOG(2.0, 64)=> 6.0000000000000000 |
LOG10(DECIMAL) => DECIMAL LOG10(DOUBLE) => DOUBLE | Base 10 logarithm, (same as LOG() with a single argument). | LOG10(1000)=> 3 |
MOD(y DECIMAL, x DECIMAL) | Modulo See notes. | MOD(5, 3)=> 2 |
PI() => DOUBLE | Return the value of PI. | PI()=> 3.141592653589793 |
POWER(x DECIMAL, y DECIMAL) => DECIMAL POWER(x DOUBLE, y DOUBLE) => DOUBLE | x raised to the power of y | POWER(27, 2)=> 729 |
ROUND(DECIMAL [s INT]) => DECIMAL ROUND(DOUBLE) => DOUBLE | Rounds to the nearest integer. s represents the optional scale accuracy | ROUND(67.2)=> 67 ROUND(67.8)=> 68 |
SIGN(DECIMAL) => DECIMAL SIGN(DOUBLE) => DOUBLE | Sign of the argument (-1, 0, or +1) | SIGN(0), SIGN(-2), SIGN(5)=> 0, -1, 1 |
SQRT(DECIMAL) => DECIMAL SQRT(DOUBLE) => DOUBLE | Square root | SQRT(9)=> 3 |
TRUNC(DECIMAL) => DECIMAL TRUNC(DOUBLE) => DOUBLE | Truncates to integer (towards zero) | TRUNC(67.89)=> 67 |
TRUNC(x DECIMAL, s INT) => DECIMAL | Truncates x to s decimal places. | TRUNC(67.89, 1)=> 67.8 |
Note: MOD(): Arguments can be either integral_type (INT, BIGINT, SMALLINT) or DECIMAL. It cannot be floating-point numbers (FLOAT or REAL)