Search
Document Center

Math functions

Last Updated: Jul 25, 2019
• 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.

• RADIANS: converts angle x in degrees to radians.

• 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:

1. select abs(-9);
2. +-------------+
3. | _col0 |
4. +-------------+
5. | 9 |

CBRT

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

• Type of the returned value: DOUBLE

• Example:

1. select cbrt(8);
2. +-------+
3. | _col0 |
4. +-------+
5. | 2.0 |

CEILING/CEIL

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

• Type of the returned value: LONG

• Example:

1. select ceiling(2.3);
2. +-------+
3. | _col0 |
4. +-------+
5. | 3 |

DEGREES

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

• Type of the returned value: DOUBLE

• Example:

1. select degrees(1.3);
2. +-------------------+
3. | _col0 |
4. +-------------------+
5. | 74.48451336700703 |

E

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

• Type of the returned value: DOUBLE

• Example:

1. select e();
2. +-------------------+
3. | _col0 |
4. +-------------------+
5. | 2.718281828459045 |

EXP

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

• Type of the returned value: DOUBLE

• Example:

1. select exp(2);
2. +-------------------+
3. | _col0 |
4. +-------------------+
5. | 7.38905609893065 |

FLOOR

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

• Type of the returned value: LONG

• Example:

1. select floor(7.8);
2. +-------+
3. | _col0 |
4. +-------+
5. | 7 |

FROM_BASE

• Command description: This command returns the value of the string interpreted as a base-radix number.

• Type of the returned value: BIGINT

• Example:

1. SELECT from_base('0110', 2);
2. +-------+
3. | _col0 |
4. +-------+
5. | 6 |
6. SELECT from_base('0110', 8);
7. +-------+
8. | 72 |
9. SELECT from_base('00a0', 16);
10. +-------+
11. | _col0 |
12. +-------+
13. | 160|

LN

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

• Type of the returned value: DOUBLE

• Example:

1. select ln(2.718281828459045);
2. +-------+
3. | _col0 |
4. +-------+
5. | 1.0 |

LOG2

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

• Type of the returned value: DOUBLE

• Example:

1. select log2(8);
2. +-------+
3. | _col0 |
4. +-------+
5. | 3.0 |

LOG10

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

• Type of the returned value: DOUBLE

• Example:

1. select log10(100);
2. +-------+
3. | _col0 |
4. +-------+
5. | 2.0 |

LOG

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

• Type of the returned value: DOUBLE

• Example:

1. select log(10,100);
2. +-------+
3. | _col0 |
4. +-------+
5. | 2.0 |

MOD

• Command description: This command returns the remainder.

• Example:

1. SELECT mod(cast(4.5 as tinyint), 3);
2. +-------+
3. | _col0 |
4. +-------+
5. | 2 |
6. SELECT mod(cast(4.5 as smallint), 3);
7. +-------+
8. | _col0 |
9. +-------+
10. | 2 |
11. SELECT mod(cast(4.5 as int), 3);
12. +-------+
13. | _col0 |
14. +-------+
15. | 2 |
16. SELECT mod(cast(4.5 as bigint), 3);
17. +-------+
18. | _col0 |
19. +-------+
20. | 2 |
21. SELECT mod(cast(4.5 as double), 3);
22. +-------+
23. | _col0 |
24. +-------+
25. | 1.5 |

PI

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

• Type of the returned value: DOUBLE

• Example:

1. select pi();
2. +-------------------+
3. | _col0 |
4. +-------------------+
5. | 3.141592653589793 |

POWER/POW

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

• Type of the returned value: DOUBLE

• Example:

1. select power(1.2,3.4);
2. +-------------------+
3. | _col0 |
4. +-------------------+
5. | 1.858729691979481 |

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

• Type of the returned value: DOUBLE

• Example:

2. +--------------------+
3. | _col0 |
4. +--------------------+
5. | 1.0471975511965976 |

RANDOM/RAND

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

• Type of the returned value: DOUBLE

• Example:

1. select random();
2. +--------------------+
3. | _col0 |
4. +--------------------+
5. | 0.5709993917553757 |
6. select random(cast(3 as tinyint));
7. +--------------------+
8. | _col0 |
9. +--------------------+
10. | 2 |
11. select random(cast(3 as smallint));
12. +--------------------+
13. | _col0 |
14. +--------------------+
15. | 1 |
16. select random(cast(3 as int));
17. +--------------------+
18. | _col0 |
19. +--------------------+
20. | 1 |
21. select random(cast(3 as bigint));
22. +--------------------+
23. | _col0 |
24. +--------------------+
25. | 1 |

ROUND

1. round(x)
2. 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:

1. SELECT round(cast(4.5 as tinyint), 3);
2. +-------+
3. | _col0 |
4. +-------+
5. | 5 |
6. SELECT round(cast(4.5 as smallint), 3);
7. +-------+
8. | _col0 |
9. +-------+
10. | 5 |
11. SELECT round(cast(4.5 as int), 3);
12. +-------+
13. | _col0 |
14. +-------+
15. | 5 |
16. SELECT round(cast(4.5 as bigint), 3);
17. +-------+
18. | _col0 |
19. +-------+
20. | 5 |
21. SELECT round(cast(4.5 as double), 3);
22. +-------+
23. | _col0 |
24. +-------+
25. | 4.5 |
26. SELECT round(cast(4.5 as tinyint));
27. +-------+
28. | _col0 |
29. +-------+
30. | 5 |
31. SELECT round(cast(4.5 as smallint));
32. +-------+
33. | _col0 |
34. +-------+
35. | 5 |
36. SELECT round(cast(4.5 as double));
37. +-------+
38. | _col0 |
39. +-------+
40. | 5.0 |

SIGN

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

• Example:

1. SELECT sign(0);
2. +-------+
3. | _col0 |
4. +-------+
5. | 0 |
6. SELECT sign(34);
7. +-------+
8. | _col0 |
9. +-------+
10. | 1 |
11. SELECT sign(-23);
12. +-------+
13. | _col0 |
14. +-------+
15. | 1 |
16. SELECT sign(1.023);
17. +-------+
18. | _col0 |
19. +-------+
20. | 1.0 |
21. SELECT sign(-1.023);
22. +-------+
23. | _col0 |
24. +-------+
25. | 1.0 |

SQRT

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

• Type of the returned value: DOUBLE

• Example:

1. select sqrt(4);
2. +-------+
3. | _col0 |
4. +-------+
5. | 2.0 |

TO_BASE

• Command description: This command returns the base-radix representation of x.

• Type of the returned value: VARCHAR

• Example:

1. SELECT to_base(8,8);
2. +-------+
3. | _col0 |
4. +-------+
5. | 10 |

TRUNCATE

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

• Type of the returned value: DOUBLE

• Example:

1. select truncate(2.3);
2. +-------+
3. | _col0 |
4. +-------+
5. | 2.0 |
6. select truncate(2.3456,2);
7. +--------+
8. | _col0 |
9. +--------+
10. | 2.3400 |
11. SELECT truncate(cast(4.5 as tinyint), 3);
12. +-------+
13. | _col0 |
14. +-------+
15. | 5 |
16. SELECT truncate(cast(4.5 as smallint), 3);
17. +-------+
18. | _col0 |
19. +-------+
20. | 5 |
21. SELECT truncate(cast(4.5 as int), 3);
22. +-------+
23. | _col0 |
24. +-------+
25. | 5 |
26. SELECT truncate(cast(4.5 as bigint), 3);
27. +-------+
28. | _col0 |
29. +-------+
30. | 5 |

WIDTH_BUCKET

1. 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:

1. SELECT WIDTH_BUCKET(5,3,4,5);
2. +--------------------+
3. | _col0 |
4. +--------------------+
5. | 6 |

Trigonometric functions

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

ACOS

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

• Type of the returned value: DOUBLE

• Example:

1. select acos(0.5);
2. +--------------------+
3. | _col0 |
4. +--------------------+
5. | 1.0471975511965979 |

ASIN

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

• Type of the returned value: DOUBLE

• Example:

1. select asin(0.5);
2. +--------------------+
3. | _col0 |
4. +--------------------+
5. | 0.5235987755982989 |

ATAN

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

• Type of the returned value: DOUBLE

• Example:

1. select atan(0.5);
2. +--------------------+
3. | _col0 |
4. +--------------------+
5. | 0.4636476090008061 |

ATAN2

1. 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:

1. select atan2(0.5,0.3);
2. +--------------------+
3. | _col0 |
4. +--------------------+
5. | 1.0303768265243125 |

COS

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

• Type of the returned value: DOUBLE

• Example:

1. select cos(1.3);
2. +---------------------+
3. | _col0 |
4. +---------------------+
5. | 0.26749882862458735 |

COSH

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

• Type of the returned value: DOUBLE

• Example:

1. select cosh(1.3);
2. +--------------------+
3. | _col0 |
4. +--------------------+
5. | 1.9709142303266285 |

SIN

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

• Type of the returned value: DOUBLE

• Example:

```sql select sin(1.3); +——————————+ | _col0 | +——————————+ | 0.963558185417193 |

1. ## TAN
2. ```sql
3. tan(double)
• Command description: This command returns the tangent of a number.

• Type of the returned value: DOUBLE

• Example:

1. select tan(8);
2. +--------------------+
3. | _col0 |
4. +--------------------+
5. | 6.799711455220379 |

TANH

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

• Type of the returned value: DOUBLE

• Example:

1. select tanh(8);
2. +--------------------+
3. | _col0 |
4. +--------------------+
5. | 0.9999997749296758 |

COT

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

• Type of the returned value: DOUBLE

• Example:

1. SELECT COT(12);
2. +--------------------+
3. | _col0 |
4. +--------------------+
5. | 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

1. 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:

1. SELECT CRC32('MySQL');
2. +--------------------+
3. | _col0 |
4. +--------------------+
5. | 3259397556 |
6. SELECT CRC32('mysql');
7. +--------------------+
8. | _col0 |
9. +--------------------+
10. | 2501908538 |

REMAINDER

1. 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:

1. SELECT REMAINDER(3.5, 2);
2. +--------------------+
3. | _col0 |
4. +--------------------+
5. | 1.5 |
6. SELECT REMAINDER(11, 4);
7. +--------------------+
8. | _col0 |
9. +--------------------+
10. | 4 |

BITAND

1. 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:

1. SELECT BITAND(5, 6);
2. +--------------------+
3. | _col0 |
4. +--------------------+
5. | 4 |
6. SELECT BITAND(cast(5.3 as int),cast(6.2 as int));
7. +--------------------+
8. | _col0 |
9. +--------------------+
10. |4 |