Loading

Mathematical functions

All math and trigonometric functions require their input (where applicable) to be numeric.

ABS(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: numeric

Description: Returns the absolute value of numeric_exp. The return type is the same as the input type.

SELECT ABS(-123.5), ABS(55); ABS(-123.5) | ABS(55) ---------------+--------------- 123.5 |55 
CBRT(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the cube root of numeric_exp.

SELECT CBRT(-125.5); CBRT(-125.5) ------------------- -5.0066577974783435 
CEIL(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: integer or long numeric value

Description: Returns the smallest integer greater than or equal to numeric_exp.

SELECT CEIL(125.01), CEILING(-125.99); CEIL(125.01) |CEILING(-125.99) ---------------+---------------- 126 |-125

E

E()

Input: none

Output: 2.718281828459045

Description: Returns Euler’s number.

SELECT E(), CEIL(E()); E() | CEIL(E()) -----------------+--------------- 2.718281828459045|3 
EXP(numeric_exp)

Input:

  1. float numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns Euler’s number at the power of numeric_exp enumeric_exp.

SELECT EXP(1), E(), EXP(2), E() * E(); EXP(1) | E() | EXP(2) | E() * E() -----------------+-----------------+----------------+------------------ 2.718281828459045|2.718281828459045|7.38905609893065|7.3890560989306495 
EXPM1(numeric_exp)

Input:

  1. float numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns Euler’s number at the power of numeric_exp minus 1 (enumeric_exp - 1).

SELECT E(), EXP(2), EXPM1(2); E() | EXP(2) | EXPM1(2) -----------------+----------------+---------------- 2.718281828459045|7.38905609893065|6.38905609893065 
FLOOR(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: integer or long numeric value

Description: Returns the largest integer less than or equal to numeric_exp.

SELECT FLOOR(125.01), FLOOR(-125.99); FLOOR(125.01) |FLOOR(-125.99) ---------------+--------------- 125 |-126 
LOG(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the natural logarithm of numeric_exp.

SELECT EXP(3), LOG(20.085536923187668); EXP(3) |LOG(20.085536923187668) ------------------+----------------------- 20.085536923187668|3.0 
LOG10(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the base 10 logarithm of numeric_exp.

SELECT LOG10(5), LOG(5)/LOG(10); LOG10(5) | LOG(5)/LOG(10) ------------------+----------------------- 0.6989700043360189|0.6989700043360187 
PI()

Input: none

Output: 3.141592653589793

Description: Returns PI number.

SELECT PI(); PI() ----------------- 3.141592653589793 
POWER( numeric_exp, integer_exp)

Input:

  1. numeric expression. If null, the function returns null.
  2. integer expression. If null, the function returns null.

Output: double numeric value

Description: Returns the value of numeric_exp to the power of integer_exp.

SELECT POWER(3, 2), POWER(3, 3); POWER(3, 2) | POWER(3, 3) ---------------+--------------- 9.0 |27.0 
SELECT POWER(5, -1), POWER(5, -2); POWER(5, -1) | POWER(5, -2) ---------------+--------------- 0.2 |0.04 
RANDOM(seed)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns a random double using the given seed.

SELECT RANDOM(123); RANDOM(123) ------------------ 0.7231742029971469 
ROUND( numeric_exp [, integer_exp])

Input:

  1. numeric expression. If null, the function returns null.
  2. integer expression; optional. If null, the function returns null.

Output: numeric

Description: Returns numeric_exp rounded to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is rounded to |integer_exp| places to the left of the decimal point. If integer_exp is omitted, the function will perform as if integer_exp would be 0. The returned numeric data type is the same as the data type of numeric_exp.

SELECT ROUND(-345.153, 1) AS rounded; rounded --------------- -345.2 
SELECT ROUND(-345.153, -1) AS rounded; rounded --------------- -350.0 
SIGN(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: [-1, 0, 1]

Description: Returns an indicator of the sign of numeric_exp. If numeric_exp is less than zero, –1 is returned. If numeric_exp equals zero, 0 is returned. If numeric_exp is greater than zero, 1 is returned.

SELECT SIGN(-123), SIGN(0), SIGN(415); SIGN(-123) | SIGN(0) | SIGN(415) ---------------+---------------+--------------- -1 |0 |1 
SQRT(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns square root of numeric_exp.

SELECT SQRT(EXP(2)), E(), SQRT(25); SQRT(EXP(2)) | E() | SQRT(25) -----------------+-----------------+--------------- 2.718281828459045|2.718281828459045|5.0 
TRUNCATE( numeric_exp [, integer_exp])

Input:

  1. numeric expression. If null, the function returns null.
  2. integer expression; optional. If null, the function returns null.

Output: numeric

Description: Returns numeric_exp truncated to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is truncated to |integer_exp| places to the left of the decimal point. If integer_exp is omitted, the function will perform as if integer_exp would be 0. The returned numeric data type is the same as the data type of numeric_exp.

SELECT TRUNC(-345.153, 1) AS trimmed; trimmed --------------- -345.1 
SELECT TRUNCATE(-345.153, -1) AS trimmed; trimmed --------------- -340.0 
ACOS(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the arccosine of numeric_exp as an angle, expressed in radians.

SELECT ACOS(COS(PI())), PI(); ACOS(COS(PI())) | PI() -----------------+----------------- 3.141592653589793|3.141592653589793 
ASIN(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the arcsine of numeric_exp as an angle, expressed in radians.

SELECT ROUND(DEGREES(ASIN(0.7071067811865475))) AS "ASIN(0.707)", ROUND(SIN(RADIANS(45)), 3) AS "SIN(45)"; ASIN(0.707) | SIN(45) ---------------+--------------- 45.0 |0.707 
ATAN(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the arctangent of numeric_exp as an angle, expressed in radians.

SELECT DEGREES(ATAN(TAN(RADIANS(90)))); DEGREES(ATAN(TAN(RADIANS(90)))) ------------------------------- 90.0 
ATAN2( ordinate, abscisa)

Input:

  1. numeric expression. If null, the function returns null.
  2. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the arctangent of the ordinate and abscisa coordinates specified as an angle, expressed in radians.

SELECT ATAN2(5 * SIN(RADIANS(45)), 5 * COS(RADIANS(45))) AS "ATAN2(5*SIN(45), 5*COS(45))", RADIANS(45); ATAN2(5*SIN(45), 5*COS(45))| RADIANS(45) ---------------------------+------------------ 0.7853981633974483 |0.7853981633974483 
COS(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the cosine of numeric_exp, where numeric_exp is an angle expressed in radians.

SELECT COS(RADIANS(180)), POWER(SIN(RADIANS(54)), 2) + POWER(COS(RADIANS(54)), 2) AS pythagorean_identity; COS(RADIANS(180))|pythagorean_identity -----------------+-------------------- -1.0 |1.0 
COSH(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the hyperbolic cosine of numeric_exp.

SELECT COSH(5), (POWER(E(), 5) + POWER(E(), -5)) / 2 AS "(e^5 + e^-5)/2"; COSH(5) | (e^5 + e^-5)/2 -----------------+----------------- 74.20994852478785|74.20994852478783 
COT(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the cotangent of numeric_exp, where numeric_exp is an angle expressed in radians.

SELECT COT(RADIANS(30)) AS "COT(30)", COS(RADIANS(30)) / SIN(RADIANS(30)) AS "COS(30)/SIN(30)"; COT(30) | COS(30)/SIN(30) ------------------+------------------ 1.7320508075688774|1.7320508075688776 
DEGREES(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Convert from radians to degrees.

SELECT DEGREES(PI() * 2), DEGREES(PI()); DEGREES(PI() * 2)| DEGREES(PI()) -----------------+--------------- 360.0 |180.0 
RADIANS(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Convert from degrees to radians.

SELECT RADIANS(90), PI()/2; RADIANS(90) | PI()/2 ------------------+------------------ 1.5707963267948966|1.5707963267948966 
SIN(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the sine of numeric_exp, where numeric_exp is an angle expressed in radians.

SELECT SIN(RADIANS(90)), POWER(SIN(RADIANS(67)), 2) + POWER(COS(RADIANS(67)), 2) AS pythagorean_identity; SIN(RADIANS(90))|pythagorean_identity ----------------+-------------------- 1.0 |1.0 
SINH(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the hyperbolic sine of numeric_exp.

SELECT SINH(5), (POWER(E(), 5) - POWER(E(), -5)) / 2 AS "(e^5 - e^-5)/2"; SINH(5) | (e^5 - e^-5)/2 -----------------+----------------- 74.20321057778875|74.20321057778874 
TAN(numeric_exp)

Input:

  1. numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the tangent of numeric_exp, where numeric_exp is an angle expressed in radians.

SELECT TAN(RADIANS(66)) AS "TAN(66)", SIN(RADIANS(66))/COS(RADIANS(66)) AS "SIN(66)/COS(66)=TAN(66)"; TAN(66) |SIN(66)/COS(66)=TAN(66) ------------------+----------------------- 2.2460367739042164|2.246036773904216