Search
Document Center

# Math functions

Last Updated: May 31, 2020
• ABS: returns the absolute value of a number.

• CBRT: returns the cube root of a number.

• CEILING/CEIL: rounds up a number to the nearest integer.

• DEGREES: converts radians into degrees.

• E: returns the constant Euler’s number.

• EXP: returns Euler’s number raised to the power of x.

• FLOOR: rounds down a number to the nearest integer.

• FROM_BASE: returns the value of the string interpreted as a base-radix number.

• LN: returns the natural logarithm of a number.

• LOG2: returns the logarithm of a number with 2 as the base.

• LOG10: returns the logarithm of a number with 10 as the base.

• LOG: returns the logarithm of x with b as the base.

• MOD: returns the remainder.

• PI: returns the constant pi.

• POWER/POW: returns x raised to the power of p.

• RANDOM/RAND: returns a pseudo-random value.

• ROUND: returns x rounded to the nearest integer, or returns x rounded to d decimal places.

• SIGN: returns the signum function of x. The result may be -1, 0, or 1.

• SQRT: returns the square root of a number.

• TO_BASE: returns the base-radix representation of x

• TRUNCATE: returns x rounded to an integer by dropping digits after the decimal point.

• WIDTH_BUCKET: returns the value of x in its range after the range delimited by the minimum value bound1 and maximum value bound2 is divided into n equal parts.

• ACOS: returns the arc cosine of a number.

• ASIN: returns the arc sine of a number.

• ATAN: returns the arc tangent of a number.

• ATAN2: returns the arc tangent of parameter 1 divided by parameter 2.

• COS: returns the cosine of a number.

• COSH: returns the hyperbolic cosine of a number.

• SIN: returns the sine of a number.

• TAN: returns the tangent of a number.

• TANH: returns the hyperbolic tangent of a number.

• COT: returns the cotangent function of a number.

• INFINITY: returns a constant that represents positive infinity.

• IS_FINITE: determines whether x is finite.

• IS_INFINITE: determines whether x is infinite.

• IS_NAN: determines whether x is not-a-number.

• NAN: returns the constant representing not-a-number.

• CRC32: calculates the cyclic redundancy check value and returns a 32-bit unsigned value. If the parameter is NULL, NULL is returned.

• REMAINDER: returns the remainder of one parameter divided by another parameter.

• BITAND: returns the result of the bitwise AND operation for two numeric values.

Note: All trigonometric functions are expressed in radians. For unit conversion, see DEGREES and RADIANS.

## ABS

• Command description: This command returns the absolute value of a number.

• Type of the returned value: the same as the input value type.

• Example:

`` select abs(-9);``    +-------------+``    | _col0       |``    +-------------+``    |   9         |``

## CBRT

``cbrt(double)``
• Command description: This command returns the cube root of a number.

• Type of the returned value: DOUBLE

• Example:

`` select cbrt(8);``  +-------+``  | _col0 |``  +-------+``  |   2.0 |``

## CEILING/CEIL

``ceiling(x)``
• Command description: This command rounds up a number to an integer.

• Type of the returned value: LONG

• Example:

`` select ceiling(2.3);``  +-------+``  | _col0 |``  +-------+``  |     3 |``

## DEGREES

``degrees(double)``
• Command description: This command converts radians into degrees.

• Type of the returned value: DOUBLE

• Example:

`` select degrees(1.3);``  +-------------------+``  | _col0             |``  +-------------------+``  | 74.48451336700703 |``

## E

``e();``
• Command description: This command returns the constant Euler’s number.

• Type of the returned value: DOUBLE

• Example:

`` select e();``  +-------------------+``  | _col0             |``  +-------------------+``  | 2.718281828459045 |``

## EXP

``exp(x);``
• Command description: This command returns Euler’s number raised to the power of x.

• Type of the returned value: DOUBLE

• Example:

`` select exp(2);``  +-------------------+``  | _col0             |``  +-------------------+``  | 7.38905609893065 |``

## FLOOR

``floor(int)``floor(bigint)``floor(double)``
• Command description: This command rounds down a number to an integer.

• Type of the returned value: LONG

• Example:

`` select floor(7.8);``  +-------+``  | _col0 |``  +-------+``  |     7 |``

## FROM_BASE

``from_base(string, radix);``
• Command description: This command returns the value of the string interpreted as a base-radix number.

• Type of the returned value: BIGINT

• Example:

`` SELECT from_base('0110', 2);``  +-------+``  | _col0 |``  +-------+``  |    6 |``  SELECT from_base('0110', 8);``  +-------+``  |    72 |``  SELECT from_base('00a0', 16);``  +-------+``  | _col0 |``  +-------+``  |    160|``

## LN

``ln(double)``
• Command description: This command returns the natural logarithm of a number.

• Type of the returned value: DOUBLE

• Example:

`` select ln(2.718281828459045);``  +-------+``  | _col0 |``  +-------+``  |   1.0 |``

## LOG2

``log2(double)``
• Command description: This command returns the logarithm of a number with 2 as the base.

• Type of the returned value: DOUBLE

• Example:

`` select log2(8);``  +-------+``  | _col0 |``  +-------+``  |   3.0 |``

## LOG10

``log10(double)``
• Command description: This command returns the logarithm of a number with 10 as the base.

• Type of the returned value: DOUBLE

• Example:

`` select log10(100);``  +-------+``  | _col0 |``  +-------+``  |   2.0 |``

## LOG

``log(x, b)``
• Command description: This command returns the logarithm of x with b as the base.

• Type of the returned value: DOUBLE

• Example:

`` select log(10,100);``  +-------+``  | _col0 |``  +-------+``  |   2.0 |``

## MOD

• Command description: This command returns the remainder.

• Example:

`` SELECT mod(cast(4.5 as tinyint), 3);``  +-------+``  | _col0 |``  +-------+``  |     2 |``  SELECT mod(cast(4.5 as smallint), 3);``  +-------+``  | _col0 |``  +-------+``  |     2 |``  SELECT mod(cast(4.5 as int), 3);``  +-------+``  | _col0 |``  +-------+``  |     2 |``  SELECT mod(cast(4.5 as bigint), 3);``  +-------+``  | _col0 |``  +-------+``  |     2 |``  SELECT mod(cast(4.5 as double), 3);``  +-------+``  | _col0 |``  +-------+``  |   1.5 |``

## PI

``pi()``
• Command description: This command returns the constant pi.

• Type of the returned value: DOUBLE

• Example:

`` select pi();``  +-------------------+``  | _col0             |``  +-------------------+``  | 3.141592653589793 |``

## POWER/POW

``power(x, p)``
• Command description: This command returns x raised to the power of p.

• Type of the returned value: DOUBLE

• Example:

`` select power(1.2,3.4);``  +-------------------+``  | _col0             |``  +-------------------+``  | 1.858729691979481 |``

``radians(x)``
• Command description: This command converts angle x in degrees to radians.

• Type of the returned value: DOUBLE

• Example:

`` select radians(60.0);``  +--------------------+``  | _col0              |``  +--------------------+``  | 1.0471975511965976 |``

## RANDOM/RAND

``random()``random(n)``
• Command description: This command returns a pseudo-random value.

• Type of the returned value: DOUBLE

• Example:

`` select random();``  +--------------------+``  | _col0              |``  +--------------------+``  | 0.5709993917553757 |``  select random(cast(3 as tinyint));``  +--------------------+``  | _col0              |``  +--------------------+``  |     2              |``  select random(cast(3 as smallint));``  +--------------------+``  | _col0              |``  +--------------------+``  |     1              |``  select random(cast(3 as int));``  +--------------------+``  | _col0              |``  +--------------------+``  |     1              |``  select random(cast(3 as bigint));``  +--------------------+``  | _col0              |``  +--------------------+``  |     1              |``

## ROUND

``round(x)``round(x, d)``
• Command description: This command returns x rounded to the nearest integer, or returns x rounded to d decimal places.

• Type of the returned value: BIGINT or DOUBLE

• Example:

`` SELECT round(cast(4.5 as tinyint), 3);``  +-------+``  | _col0 |``  +-------+``  |     5 |``  SELECT round(cast(4.5 as smallint), 3);``  +-------+``  | _col0 |``  +-------+``  |     5 |``  SELECT round(cast(4.5 as int), 3);``  +-------+``  | _col0 |``  +-------+``  |     5 |``  SELECT round(cast(4.5 as bigint), 3);``  +-------+``  | _col0 |``  +-------+``  |     5 |``  SELECT round(cast(4.5 as double), 3);``  +-------+``  | _col0 |``  +-------+``  |   4.5 |``  SELECT round(cast(4.5 as tinyint));``  +-------+``  | _col0 |``  +-------+``  |     5 |``  SELECT round(cast(4.5 as smallint));``  +-------+``  | _col0 |``  +-------+``  |     5 |``  SELECT round(cast(4.5 as double));``  +-------+``  | _col0 |``  +-------+``  |   5.0 |``

## SIGN

``sign(x)``
• Command description: This command returns the signum function of x. The result may be -1, 0, or 1.

• Example:

`` SELECT sign(0);``  +-------+``  | _col0 |``  +-------+``  |     0 |``  SELECT sign(34);``  +-------+``  | _col0 |``  +-------+``  |     1 |``  SELECT sign(-23);``  +-------+``  | _col0 |``  +-------+``  |    -1 |``  SELECT sign(1.023);``  +-------+``  | _col0 |``  +-------+``  |   1.0 |``  SELECT sign(-1.023);``  +-------+``  | _col0 |``  +-------+``  |   -1 |``

## SQRT

``sqrt(double)``
• Command description: This command returns the square root of a number.

• Type of the returned value: DOUBLE

• Example:

`` select sqrt(4);``  +-------+``  | _col0 |``  +-------+``  |   2.0 |``

## TO_BASE

``to_base(x, radix)``
• Command description: This command returns the base-radix representation of x.

• Type of the returned value: VARCHAR

• Example:

`` SELECT to_base(8,8);``  +-------+``  | _col0 |``  +-------+``  | 10    |``

## TRUNCATE

``truncate(double)``
• Command description: This command returns x rounded to integer by dropping digits after decimal point.

• Type of the returned value: DOUBLE

• Example:

`` select truncate(2.3);``  +-------+``  | _col0 |``  +-------+``  |   2.0 |``  select truncate(2.3456,2);``  +--------+``  | _col0  |``  +--------+``  | 2.3400 |``  SELECT truncate(cast(4.5 as tinyint), 3);``  +-------+``  | _col0 |``  +-------+``  |     5 |``  SELECT truncate(cast(4.5 as smallint), 3);``  +-------+``  | _col0 |``  +-------+``  |     5 |``  SELECT truncate(cast(4.5 as int), 3);``  +-------+``  | _col0 |``  +-------+``  |     5 |``  SELECT truncate(cast(4.5 as bigint), 3);``  +-------+``  | _col0 |``  +-------+``  |     5 |``

## WIDTH_BUCKET

``width_bucket(x, bound1, bound2, n);``
• Command description: This command returns the value of x in its range after the range delimited by the minimum value bound1 and maximum value bound2 is divided into n equal parts.

• Type of the returned value: BIGINT

• Example:

`` SELECT  WIDTH_BUCKET(5,3,4,5);``  +--------------------+``  | _col0              |``  +--------------------+``  | 6                  |``

## Trigonometric functions

All trigonometric functions are expressed in radians. For unit conversion, see DEGREES and RADIANS.

## ACOS

``acos(double)``
• Command description: This command returns the arc cosine of a number.

• Type of the returned value: DOUBLE

• Example:

`` select acos(0.5);``  +--------------------+``  | _col0              |``  +--------------------+``  | 1.0471975511965979 |``

## ASIN

``asin(double)``
• Command description: This command returns the arc sine of a number.

• Type of the returned value: DOUBLE

• Example:

``  select asin(0.5);``  +--------------------+``  | _col0              |``  +--------------------+``  | 0.5235987755982989 |``

## ATAN

``atan(double)``
• Command description: This command returns the arc tangent of a number.

• Type of the returned value: DOUBLE

• Example:

`` select atan(0.5);``  +--------------------+``  | _col0              |``  +--------------------+``  | 0.4636476090008061 |``

## ATAN2

``atan2(double, double)``
• Command description: This command returns the arc tangent of parameter 1 divided by parameter 2.

• Type of the returned value: DOUBLE

• Example:

`` select atan2(0.5,0.3);``  +--------------------+``  | _col0              |``  +--------------------+``  | 1.0303768265243125 |``

## COS

``cos(double)``
• Command description: This command returns the cosine of a number.

• Type of the returned value: DOUBLE

• Example:

`` select cos(1.3);``  +---------------------+``  | _col0               |``  +---------------------+``  | 0.26749882862458735 |``

## COSH

``cosh(double)``
• Command description: This command returns the hyperbolic cosine of a number.

• Type of the returned value: DOUBLE

• Example:

`` select cosh(1.3);``  +--------------------+``  | _col0              |``  +--------------------+``  | 1.9709142303266285 |``

## SIN

``sin(x);``
• Command description: This command returns the sine of a number.

• Type of the returned value: DOUBLE

• Example：

``  select sin(1.3);``  +--------------------+``  | _col0              |``  +--------------------+``  | 0.963558185417193 |``

## TAN

``tan(double)``
• Command description: This command returns the tangent of a number.

• Type of the returned value: DOUBLE

• Example:

``  select tan(8);``  +--------------------+``  | _col0              |``  +--------------------+``  | -6.799711455220379 |``

## TANH

``tanh(double)``
• Command description: This command returns the hyperbolic tangent of a number.

• Type of the returned value: DOUBLE

• Example:

`` select tanh(8);``  +--------------------+``  | _col0              |``  +--------------------+``  | 0.9999997749296758 |``

## COT

``cot(x);``
• Command description: This command returns the cotangent function of a number.

• Type of the returned value: DOUBLE

• Example:

`` SELECT COT(12);``  +--------------------+``  | _col0              |``  +--------------------+``  | 1.5726734063976893 |``

## INFINITY

• Command description: This command returns a constant that represents positive infinity.

• Type of the returned value: DOUBLE

## IS_FINITE

• Command description: This command determines whether x is finite.

• Type of the returned value: BOOLEAN

## IS_INFINITE

• Command description: This command determines whether x is infinite.

• Type of the returned value: BOOLEAN

## IS_NAN

• Command description: This command determines whether x is not-a-number.

• Type of the returned value: BOOLEAN

## NAN

• Command description: This command returns the constant representing not-a-number.

• Type of the returned value: DOUBLE

## CRC32

``CRC32(expr);``
• Command description: This command calculates the cyclic redundancy check value and returns a 32-bit unsigned value. If the parameter is NULL, NULL is returned.

• Type of the returned value: DOUBLE

• Example:

`` SELECT CRC32('MySQL');``  +--------------------+``  | _col0              |``  +--------------------+``  | 3259397556         |``  SELECT CRC32('mysql');``  +--------------------+``  | _col0              |``  +--------------------+``  | 2501908538         |``

## REMAINDER

``REMAINDER(n1, n2);``
• Command description: This command returns the remainder of one parameter divided by another parameter.

• Type of the returned value: DOUBLE or BIGINT

• Example:

`` SELECT REMAINDER(3.5, 2);``  +--------------------+``  | _col0              |``  +--------------------+``  | -1.5               |``  SELECT REMAINDER(11, 4);``  +--------------------+``  | _col0              |``  +--------------------+``  | 4                  |``

## BITAND

``BITAND(expr1, expr2);``
• Command description: This command returns the result of the bitwise AND operation for two numeric values.

If any of the parameters in a function is not of the integer type, you need to convert the parameter into the integer type before bitwise AND operation.

• Type of the returned value: BIGINT

• Example:

`` SELECT BITAND(5, 6);``  +--------------------+``  | _col0              |``  +--------------------+``  | 4               |``  SELECT BITAND(cast(5.3 as int),cast(6.2 as int));``  +--------------------+``  | _col0              |``  +--------------------+``  |4                   |``