Analytics-Plus Help

 

MySQL MATHEMATICAL FUNCTIONS

The following table lists all the MySQL in-built Math functions supported by ManageEngine Reports. Click on any function to know more about the same.

ABSACOSASINATANATAN2CEIL
CEILINGCONVCOSCOTCRC32DEGREES
EXPFLOORFORMATLNLOGLOG2
LOG10MODOCTPIPOWRADIANS
RANDROUNDSIGNSINSQRTTAN
TRUNCATE

ABS(numeric_arg):

Purpose :

Returns the numerical value of a number given in the argument without regard of its sign.

Example :

Select ABS (-23) returns '23'

Select ABS ('-23') returns '23.0'                   // If a number is passed as a string then the number is returned in decimal form.

Select ABS ('-23B0011') returns '23.0'                   // As soon as a non numeric character is found in the number string the number is returned in decimal form.

Note :

  • Returns '0.0' for non-numeric string arguments.
  • Returns '0.0' for null arguments.

ACOS(numeric_arg):

Purpose :

Returns the Inverse Cosine value of the argument passed. The argument's value should be between -1 to 1.

Example :

Select ACOS (0.5) returns '1.0471975511966'

Select ACOS ('0.5A@5') returns '1.0471975511966'                // As soon as a non numeric character is found in the number string the number value is returned.

Note :

  • Returns the value of Acos(0) i.e '1.5707963267949' for null argument.
  • Returns the value of Acos(0) i.e '1.5707963267949' for non numeric argument.
  • Returns null if the numeric argument value is out of range.

ASIN(numeric_arg):

Purpose :

Returns the Inverse Sine value of the argument passed. The argument's value shuld be between -1 to 1.

Example :

Select ASIN (0.5) returns '0.5235987755983'

Select ASIN ('0.5A@5') returns '0.5235987755983'               // As soon as a non numeric character is found in the number string the number's value is returned.

Note :

  • Returns '0.0' for null argument.
  • Returns '0.0' for non numeric argument.
  • Returns null if the numeric argument value is out of range.

ATAN(numeric_arg):

Purpose :

Returns the Inverse Tangent value of the argument passed.

Example :

Select ATAN (0.5) returns '0.46364760900081'

Select ATAN ('0.5A@5') returns '0.46364760900081'                // As soon as a non numeric character is found in the number string the number's value is returned.

Note :

  • Returns '0.0' for null argument.
  • Returns '0.0' for non numeric argument.
  • A negative number's ATAN value is equal to the ATAN value of its positive number but with a minus sign.

ATAN2(numeric_arg):

Purpose :

Returns the number of characters present in the argument.

Example :

Select ATAN2 (2,4) returns '0.46364760900081'

Select ATAN2 ('0.5A') returns '0.46364760900081'                  // As soon as a non numeric character is found in the number string the number's value is returned.

Note :

  • Returns '0.0' if one argument is null and the other argument is a non numeric number.
  • Returns '0.0' if both the arguments are null or zero.
  • Returns '1.5707963267949' when the second argument is '0' and the first argument is any natural number.
  • Returns '0.0' when the second argument is '0' and the first argument is any non-numeric character and vice versa.

CEIL(numeric_arg):

Purpose :

The smallest integer that is greater than or equal to the value of the argument is returned.

Example :

Select CEIL (1.5) returns '2'

Note :

  • Returns '0' if argument is null or any non-numeric character.
  • As soon as a non numeric character is found in the number string argument, the CEILING of the number value is returned and the rest of the argument characters are neglected.

CEILING(numeric_arg):

Purpose :

The smallest integer that is greater than or equal to the value of the argument is returned.

Example :

Select CEILING (1.5) returns '2'

Note :

  • Returns '0' if argument is null or any non-numeric character.
  • As soon as a non numeric character is found in the number string argument, the CEILING of the number value is returned and the rest of the argument characters are neglected.

CONV(string_arg, numeric_arg1, numeric_arg2):

Purpose :

Changes the argument number string from the given base to the required base. The first argument is the given number string, the second argument is the original base of the number and the third argument is the base to which the number string is to be converted.

Example :

Select CONV ('F',16,10) returns '15'

Note :

  • Returns null if any of the arguments is null.
  • Returns '0' if the bases given in the second or third arguments are non-compatible with the number.
  • The maximum value of the bases is 36 and the minimum value is 2.

COS(numeric_arg):

Purpose :

Returns the COSINE value of the argument passed. The value of the arguments should be in radians.

Example :

Select COS (45) returns '0.52532198881773'

Note :

  • Returns '1.0' if the argument is null, zero or any non-numeric string.

COT(numeric_arg):

Purpose :

Returns the COTANGENT value of the argument passed.

Example :

Select COT() returns ''

Note :

  • Returns null if the argument is null, zero or any non-numeric string.

CRC32(numeric_arg):

Purpose :

Returns a 32 bit unsigned output after calculating the cyclic redundency check.

Example :

Select CRC32 ('111') returns '1298878781'

Note :

  • Returns '0' if the argument is null.
  • The argument should be a string. Numbers are also taken as strings.

DEGREES(numeric_arg):

Purpose :

The argument's value is converted from radians to degrees.

Example :

Select DEGREES (3.141593) returns '180'

Note :

  • Returns null if the argument is zero, null or a non-numeric string.

EXP(numeric_arg):

Purpose :

The value of 'e' raised to the power of the given number argument is returned.

Example :

Select EXP (6) returns '403.42879349274'

Note :

  • Returns '1.0' when zero, null or any non-numeric number is passed in the argument.

FLOOR(numeric_arg):

Purpose :

The largest integer that is smaller than or equal to the value of the argument is returned.

Example :

Select FLOOR (1.5) returns '1'

Note :

  • Returns '0' if argument is null or any non-numeric character.
  • As soon as a non numeric character is found in the number string argument, the FLOOR of the number value is returned and the rest of the argument characters are neglected.

FORMAT(numeric_arg, numeric_arg):

Purpose :

Rounds off the number given in the first argument to the number of decimals given in the second argument.

Example :

SELECT FORMAT (1.0001111,5) returns '1.00011'

Note :

  • If the second argument is 0 then there are no decimal value or decimal point in the returned number.
  • If the number has no decimals then zeros are appended to the number after the decimal point.

LN(numeric_arg):

Purpose :

Returns the natural logarithmic value of the given argument.

Example :

Select LN('123') returns '4.8121843553724'

Note :

  • Returns null if the argument is null, negative or any non-numeric character.
  • As soon as a non numeric character is found in the number string argument, the LN of the number value is returned and the rest of the argument characters are neglected.

LOG(number1, number2):

Purpose :

Performs the same function as LN for single parameter. For 2 parameters, the first argument is the base and the second argument is the number. The logarithm of the number to the base is returned.

Example :

Select LOG(10,100) returns 2.0

Note :

  • Only numbers are accepted. Numbers given as strings are also not accepted.

(numeric_arg):

Purpose :

The logarithm of the number to the base 2 is returned.

Example :

Select LOG2 (100) returns '6.6438561897747'

Note :

  • Returns null if the argument is null, negative or any non-numeric charcter.
  • Numbers given as strings are also accepted.
  • As soon as a non numeric character is found in the number string argument, the LOG2 of the number is returned and the rest of the argument characters are neglected.

LOG10(numeric_arg):

Purpose :

The logarithm of the number to the base 10 is returned.

Example :

Select LOG10 (100) returns '2.0'

Note :

  • Returns null if the argument is null, negative or any non-numeric character.
  • Numbers given as strings are also accepted.
  • As soon as a non numeric character is found in the number string argument, the LOG10 of the number is returned and the rest of the argument characters are neglected.

MOD(numeric_arg,numeric_arg):

Purpose :

The first argument is the divident and the second argument is the divisor. The remainder of the division is returned.

Example :

Select MOD (99,8) returns '3'

Select 99 mod 8 returns '3'

Select 99 % 8 returns '3'

Note :

  • Returns '0.0' if the first argument is null and the second argument is any number.
  • Returns null if the second argument is null irrespective of the first argument.

OCT(numeric_arg):

Purpose :

Returns the octal value of the number given in the argument.

Example :

Select OCT (12) returns '14'.

Note :

  • Returns '0' if the argument is null or non-numeric character string.
  • As soon as a non numeric character is found in the number string argument, the OCT of the number is returned and the rest of the argument characters are neglected.

PI():

Purpose :

The value of ¶ (pi), ie 22 / 7 is returned. Only 6 numbers after the decimal point is returned by default.

Example :

Select PI () returns '3.141593'

>POW(numeric_arg, numeric_arg):

RADIANS(numeric_arg):

Purpose :

The argument's value is converted from degree to radians.

Example :

Select RADIANS ( 180) returns '3.1415926535898'

RAND(numeric_arg):

Purpose :

A random value between the range of 0 and 1 is returned for the given argument.

Example :

Select RAND (3) returns '0.90576975597606'

ROUND(numeric_arg):

Purpose :

Returns the rounded value of the given decimal number.

Example :

Select ROUND (23.248) returns '23'

Select ROUND (23.248, 1) returns '23.4'                   // Rounds off the given number's decimals upto the number given in the second argument.

Select ROUND (23.248,-1) returns '20.0'

SIGN(numeric_arg):

Purpose :

Depending on the sign of the argument, -1 is returned for negative number, 0 is returned for zero and 1 is returned for positive number.

Example :

Select SIGN (-200) returns '-1'

Select SIGN (200) returns '1'

Select SIGN (-0) returns '0' / returns '0' irrespective of the sign before 0.

Note :

  • Returns '0' if the given argument is a non-numeric string.
  • If a string has numbers followed by characters then the sign of the number is returned.

SIN(numeric_arg):

Purpose :

Returns the SINE value of the argument passed. The value of the arguments should be in radians

Example :

Select SIN ( 111) returns '0.86455144861061'

Note :

  • Returns '0.0' for null and non-numeric arguments.
  • As soon as a non numeric character is found in the number string the number's SIN value is returned.

SQRT(numeric_arg):

Purpose :

Returns the square root value of the given number.

Example :

Select SQRT ('2') returns '1.4142135623731'

Note :

  • Returns null for non numeric string arguments.
  • Returns '0.0' for negative numbers.
  • Returns the SQRT of the number alone when a number is folllowed by a non-numeric string.

TAN(numeric_arg):

Purpose :

Returns the TAN value of the argument passed. The value of the arguments should be in radians.

Example :

Select TAN ( '111') returns '1.7203486651304'

Note :

  • Returns '0.0' for non-numeric or null arguments.
  • For negative numbers, the TAN of the argument's absolute value is found and a minus sign is added at the beginning.

TRUNCATE(string_arg):

Purpose :

The first argument is the given decimal number and the second argument is the number that decides upto which the given number's decimals are to be truncated. The truncated number is returned.

Example :

Select TRUNCATE (234.56789,2) returns '234.56'

Select TRUNCATE (234.56789,'-2') returns '200.0'

Note :

  • Truncates all the decimals if the second argument is null, zero or any non-numeric string.

Share this post : FacebookTwitter