MySQL Numeric Functions

MySQL ABS() Function

Main Article :- MySql ABS() Function

The ABS() function calculates the absolute (positive) value of an specified integer.

If the input parameter is NULL, the absolute value will also be NULL.

Because the result cannot be saved in a signed BIGINT integer, ABS(-9223372036854775808) throws an error.

Syntax:

Here is the syntax of MySQL ABS() function:

ABS(numeric value)

Parameter Values

Parameter Description
numeric value Required. An specific number.

Return Value:

  • Convert the leading numbers to a number if it starts with a number.
  • Convert it to 0 if it can't be converted to a number.
  • ABS() returns NULL if the number parameter is NULL.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: The example to Find the absolute value of a Positive and Negative number:

SELECT ABS(-143.6) AS AbsoluteValueOfNegativeNumber, 
ABS(143.6) AS AbsoluteValueOfPositiveNumber;

Result:

AbsoluteValueOfNegativeNumber AbsoluteValueofPositiveNumber
143.6 143.6

This result calculates the absolute value of a positive number (143.6) and a negative number (-143.6), so the absolute value of the result is 143.6.

Example 2: Let's see how to find the absolute value of various parameters:

SELECT ABS('420'),
ABS('-420'),
ABS('-420J'),
ABS('J420'),
ABS(NULL);

Result:

ABS('420') ABS('-420') ABS('-420J') ABS('J420') ABS('020'), ABS(NULL)
420 420 420 0 20 NULL

If the parameter value starts with a number like '420', '-420', '-420J', or '020', the absolute number '420' will be the output. If the value starts with the text 'J420', the output will be 0. and if the input value is NULL, the result is also NULL.


MySQL ACOS() Function

The ACOS() function takes a value whose cosine is X and returns it as the arc cosine of specific value.

If the given value is not between -1 and 1, this function will return NULL.

Syntax:

Here is the syntax of MySQL ACOS() function:

ACOS(numeric value)

Parameter Values

Parameter Description
numeric value Required. A number.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: This example gets the numbers arc cosine:

SELECT ACOS(0.3),
ACOS(0.75);

Result:

ACOS(0.3) ACOS(0.75)
1.2661036727795 0.722734247813416

As per the result, the specified values 0.3, and 0.75 return its arc cosine value.

Example 2: Let's implement different values in this example:

SELECT ACOS(-0.7),
ACOS(-0.3),
ACOS(1),
ACOS(0),
ACOS(-1);

Result:

ACOS(-0.7) ACOS(-0.3) ACOS(1) ACOS(0) ACOS(-1)
2.34619382340565 1.87548898081029 0 1.5707963267949 3.14159265358979

From the result, you can see that if you gave negative numbers like -0.7, -0.3, and -1 will return the corresponding arc cosine value.

Example 3: Let's give range above or less than range -1 to 1 in this example:

SELECT ACOS(3),
ACOS(-3),
ACOS(NULL);

Result:

ACOS(3) ACOS(-3) ACOS(NULL)
NULL NULL NULL

When you give the input value outside the range of -1 to 1. it will return NULL, as well as if the specified value is null, the output will be NULL.


MySQL ASIN() Function

The ASIN() function takes a value whose sine is X and returns it as the arc sine of specific value.

If the given value is not between -1 and 1, this function will return NULL.

Syntax:

Here is the syntax of MySQL ASIN() function:

ASIN(number)

Parameter Values

Parameter Description
numeric value Required. A numer.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: This example gets the numbers arc sine:

SELECT ASIN(0.3),
ASIN(0.75);

Result:

ASIN(0.3) ASIN(0.75)
0.304692654015398 0.848062078981481

As per the result, the specified values 0.3, and 0.75 return its arc sine value.

Example 2: Let's implement different values in this example:

SELECT ASIN(-0.7),
ASIN(-0.3),
ASIN(1),
ASIN(0),
ASIN(-1);

Result:

ASIN(-0.7) ASIN(-0.3) ASIN(1) ASIN(0) ASIN(-1)
-0.775397496610753 -0.304692654015398 1.5707963267949 0 -1.5707963267949

From the result, you can see that if you gave negative numbers like -0.7, -0.3, and -1 will return the corresponding arc sine value. When you give the input value outside the range of -1 to 1, it will return NULL, as well as if the specified value is null, the output will be NULL.

Example 3: Let's give range above or less than range -1 to 1 in this example:

SELECT ASIN(3),
ASIN(-3),
ASIN(NULL);

Result:

ASIN(3) ASIN(-3) ASIN(NULL)
NULL NULL NULL

When you give the input value outside the range of -1 to 1. it will return NULL, as well as if the specified value is null, the output will be NULL.


MySQL ATAN() Function

The ATAN() function returns the arc tangent of one or more integers.

If any parameter is NULL, the ATAN() function will return NULL.

Syntax:

Here is the syntax of MySQL ATAN() function:

ATAN(numeric value)

or

ATAN(x, y)

Parameter Values

Parameter Description
number A numeric value.
x, y You'll need two numbers to compute the arc tangent of a circle.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: This example gets the numbers arc tangent:

SELECT ATAN(3.3);

Result:

1.27656176168371

As per the result, the specified value 3.3 return its arc tangent value 1.27656176168371.

Example 2: In this example let's check the tangent value of the negative number:

SELECT ATAN(0.3),
ATAN(-0.7),
ATAN(-0.3);

Result:

ATAN(0.3) ATAN(-0.7) ATAN(-0.3)
0.291456794477867 -0.610725964389209 -0.291456794477867

From the result, you can see that if you gave negative numbers like -0.3, -0.7 will return the corresponding arc tangent value.

Example 2: The arc tangent of two numbers is returned in this example:

SELECT ATAN(-0.7, 3);

Result:

-0.229231933276995

The example returns -0.229231933276995 as an arc tangent of the two variables -0.7 and 3.

Example 3: The arc tangent of pi function and variable returned in this example:

SELECT ATAN(PI(), 2);

Result:

1.00388482185389

The example returns 1.003882185389 as an arc tangent of the two variables PI() value and 2.

Example 4: The arc tangent of NULL value returned in this example

SELECT ATAN(NULL);

Result:

NULL

As this function works, if the any parameter is null, a ATAN() will return null.


MySQL ATAN2() Function

The arc tangent of two integers is returned by the ATAN2() function.

Calculating the arc tangent of x / y is similar, only the quadrant of the result is determined by the signs of both variables.

Syntax:

Here is the syntax of MySQL ATAN2() function:

ATAN2(x, y)

Parameter Values

Parameter Description
x, y Required. You'll need two numbers to compute the arc tangent of a circle.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: The arc tangent of two values is returned:

SELECT ATAN2(2.5, 1),
ATAN2(0.25, 1);

Result:

ATAN2(2.5, 1) ATAN2(0.25, 1)
1.19028994968253 0.244978663126864

The output returns the quadrant of the two variables 2.5, 1, and 0.25, 1.

Example 2: The arc tangent of two numbers is returned, let's use one negative number in this example:

SELECT ATAN(-3,3);

Result:

-0.785398163397448

The result will return -0.785398163397448 as an tangent value of negative number -2 and positive value 2.

Example 3: Let's use Null value in ATAN2() function:

SELECT ATAN(NULL);

Result:

NULL

Whenever you use null in ATAN2() it will return NULL.


MySQL CEIL() Function

The CEIL() function in MySQL returns the lowest integer value that is greater than or equal to the given number.

Note: The CEIL() function is the same as the CEILING() function.

Syntax:

Here is the syntax of MySQL CEIL() function:

CEIL(numeric value)

Parameter Values

Parameter Description
number Required. A number.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: The lowest integer number greater than or equal to 47.89 is found in this example:

SELECT CEIL(47.89), CEIL(47.22);

Result:

CEIL(47.89) CEIL(47.22)
48 48

Through the result, the function returns greater than the specified Numeric value of 47.89.

Example 2: The lowest integer number greater than or equal to 75 is found in this example:

SELECT CEIL(75);

Result:

75

Through the result, the function returns an equal value to the specified Numeric value of 75.

Example 3: Let's use some more example:

CEIL(-143.321),
CEIL(-143.567),
CEIL(-143),
CEIL(-400),
CEIL(NULL);

Result:

CEIL(-143.321) CEIL(-143.567) CEIL(-143) CEIL(-400) CEIL(NULL)
-143 -143 -143 -400 NULL

You can see from the output that if we provide negative values, it returns the typical greater than or equal values. If a NULL exists, it is always NULL.


MySQL CEILING() Function

Main Article :- MySql CEILING() Function

The CEILING() function in MySQL returns the lowest integer value that is larger than or equal to the supplied number.

If the parameter number is NULL, the CEILING() function will return NULL.

Note: This method is the same as CEIL().

Syntax:

Here is the syntax of MySQL CEILING() function:

CEILING(numeric value)

Parameter Values

Parameter Description
numeric value Required. A number.

Technical Details

Works in: From MySQL 4.0

Example

Example 1: The lowest integer number greater than or equal to 45.89 is found in this example:

SELECT CEILING(45.89), CEILING(45.22);

Result:

CEILING(45.89) CEILING(45.22)
46 46

Through the result, the function returns greater than the specified Numeric value of 45.89.

Example 2: The lowest integer number greater than or equal to 55 is found in this example:

SELECT CEILING(55);

Result:

55

Through the result, the function returns an equal value to the specified Numeric value of 55.

Example 3: Let's use some more example:

SELECT CEILING(-143.321),
CEILING(-143.567),
CEILING(-143),
CEILING(-400),
CEILING(NULL);

Result:

CEILING(-143.321) CEILING(-143.567) CEILING(-143) CEILING(-400) CEILING(NULL)
-143 -143 -143 -400 NULL

You can see from the output that if we provide negative values, it returns the typical greater than or equal values. If a NULL exists, it is always NULL.


MySQL COS() Function

The COS() method returns the cosine of an integer.

If the parameter number is NULL, the COS() function will return NULL.

Syntax:

Here is the syntax of MySQL COS() function:

COS(number)

Parameter Values

Parameter Description
number Required. A numeric value

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: The example to return cosine value:

SELECT COS(2),  
COS(2.5),
COS(0.2);

Result:

COS(2) COS(2.5) COS(0.2)
-0.416146836547142 -0.801143615546934 0.980066577841242

Via the result, you can see we give a positive value and returns cosine corresponding value.

Example 2: let's use negative number, pi(), 0, and NUll values to check the cosine value:

SELECT COS(-0.6),
COS(-0.3),
COS(0),
COS(PI()),
COS(NULL);

Result:

COS(-0.6) COS(-0.3) COS(0) COS(pi()) COS(NULL)
0.825335614909678 0.955336489125606 1 -1 NULL

You can see we give a negative value and return the cosine corresponding value. If we give 0 its cosine value, 1 is returned, the pi() function value is -1. As mentioned above, if any of the parameters is null in the cos, it will return null.


MySQL COT() Function

The COT() method returns the cotangent of an integer.

Syntax:

Here is the syntax of MySQL COT() function:

COT(numeric value)

Parameter Values

Parameter Description
numeric value Required. It's a number. If the number is zero, the result is either an error or NULL.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: Returns the cotangent of an integer, for example:

SELECT COT(5);

Result:

-0.295812915532746

The value of contangent 5 is -0.295812915532746 as a consequence of the calculation.

Example 2: Let's use some negative number, postive number, and pi for example::

SELECT COT(2.5),
COT(0.2),
COT(-0.5),
COT(-0.2),
COT(PI());

Output:

COT(2.5) COT(0.2) COT(-0.5) COT(-0.2) COT(PI())
0.5984721441039565 0.19866933079506122 -0.479425538604203 -0.19866933079506122 1.2246467991473532e-16

Via the result, you can see we give a positive value and returns contagent corresponding value.

Example 3: Check the 0 and NULL values in the example:

SELECT COT(0),
COT(NULL);

Result:

COT(0) COT(NULL)
0 NULL

The COT() function returns NULL if the parameter number is NULL.


MySQL DEGREES() Function

The DEGREES() function in MySQL translates a radian number to a degree and returns the result.

The DEGREES() function returns NULL if the number parameter is NULL.

Note: RADIANS() and PI() are also available.

Syntax:

Here is the syntax of MySQL DEGREES() function:

DEGREES(numeric value)

Parameter Values

Parameter Description
numeric value Required. A number.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: The following formula can be used to convert radian values to degrees:

SELECT DEGREES(2.8);

Result:

160.42818263663

Convert radian value 2.8 to degrees 160.42 using the result.

Example 2: Let's use the radian Convert the pi() function's radian value to degrees:

SELECT DEGREES(PI()*2);

Result:

360

From the result, the pi function as a radian value convert to degrees.


MySQL DIV Function

The DIV function in MySQL divides two numbers and produces a result that is an integer.

Syntax:

Here is the syntax of MySQL DIV() function:

a DIV b

Parameter Values

Parameter Description
a Required. The result of dividing a value by b.
b Required. The divisor.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: Example for Integer division (15/3):

SELECT 15 DIV 3;

Result:

15 div 3
5

From the result, two values are 15/3. The value will be 5. It only returns cosent.

Example 2: Example for Integer division (9/2):

SELECT 9 DIV 2;

Result:

4

The result will be cosent 4.


MySQL EXP() Function

EXP() delivers the value of the natural constant e raised to the power of the provided value in MySQL. for example, EXP(2) yields e2. The natural logarithm's base, often known as the natural base, is e.

The constant e is used in natural logarithms (2.718281...).

Tip: Examine the LOG() and LN() methods, as well.

Syntax:

Here is the syntax of MySQL EXP() function:

EXP(numeric value)

Parameter Values

Parameter Description
numeric value Required. A number.

Technical Details

Works in: From MySQL 4.0

Example

Example 1: Return e raised to the power of 1 example:

SELECT EXP(1);

Result:

2.71828182845905

The result is given that power of 1.

Example 2: Let's try some new expression values:

SELECT EXP(0),
EXP(2),
EXP(-1),
EXP(-2),
EXP(NULL);

Result:

EXP(0) EXP(2) EXP(-1) EXP(-2) EXP(NULL)
1 7.38905609893065 0.367879441171442 0.135335283236613 NULL

Through the result, you can use the exp() function to raise the power of the negative number, 0, NULL.


MySQL FLOOR() Function

Main Article :- MySql FLOOR() Function

The FLOOR() method returns the largest integer value that is less than or equal to the provided integer.

The return value for exact-value numeric parameters is of the exact-value numeric type. The return value for string or floating-point parameters is of the floating-point type.

Note: Also look at the ROUND(), CEIL(), CEILING(), TRUNCATE(), and DIV functions.

Syntax:

Here is the syntax of MySQL FLOOR() function:

FLOOR(numeric value)

Parameter Values

Parameter Description
numeric value Required. A number.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: Return the largest integer number that is less than or equal to 143.89 and 145.22:

SELECT FLOOR(145.89), FLOOR(145.22);

Result:

FLOOR(145.89) FLOOR(145.22)
145 145

The result will give less than or equal to 145.89 and 145.22.

Example 2: Let's use different numeric values in this function:

SELECT FLOOR(143),
FLOOR(-143.123),
FLOOR(-143.789),
FLOOR(-143),
FLOOR(-200),
FLOOR(NULL);

Result:

FLOOR(143) FLOOR(-143.123) FLOOR(-143.789) FLOOR(-143) FLOOR(-200) FLOOR(NULL)
143 -144 -144 -143 -200 NULL

We returned the floor values using negative integers -143.123, -143.789, -200, and whole numbers 143. It will return NULL if we use a NULL value.


MySQL GREATEST() Function

The GREAEST() function in MySQL returns the highest value in the parameter list.

Note: The LEAST() method can be used to locate the smallest value in a parameter list.

Syntax:

Here is the syntax of MySQL GREATEST() function:

GREATEST(param1, param2, param3, ...)

Parameter Values

Parameter Description
param1, param2, param3, ... Required. The set of parameters to be evaluated.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: In this case, the highest-valued parameters in the list should be returned:

SELECT GREATEST(4, 22, 64, 9, 55);

Result:

64

The greatest function takes the highest value 64 from the list as a result.

Example 2: The most relevant argument in the list is returned in this example:

SELECT GREATEST("simmanchith.com", "jp.com", "developer.com");

Result:

simmanchith.com

In this result, you can see that this function returns the most significant parameter in the list.

Example 3: Let's use NULL values in GREATEST function:

GREATEST('j', 'p', NULL);

Result:

NULL

This result demonstrates that if the list contains a NULL, the result will be NULL.


MySQL LEAST() Function

The LEAST() function in MySQL returns the smallest value in the parameter list.

Note: The LEAST() method can be used to locate the smallest value in a parameter list.

Syntax:

Here is the syntax of MySQL LEAST() function:

LEAST(param1, param2, param3, ...)

Parameter Values

Parameter Description
param1, param2, param3, ... Required. The set of parameters.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: In this case, the smallest-valued parameters in the list should be returned:

SELECT LEAST(4, 22, 64, 9, 55);

Result:

4

The least function takes the lowest value 4 from the list as a result.

Example 2: The most relevant argument in the list is returned in this example:

SELECT LEAST("simmanchith.com", "jp.com", "developer.com");

Result:

developer.com

In this result, you can see that this function returns the least significant parameter in the list.

Example 3: Let's use NULL values in LEAST function:

GREATEST('j', 'p', NULL);

Result:

NULL

This result demonstrates that if the list contains a NULL, the result will be NULL.


MySQL LN() Function

LN() returns the natural logarithm of an Integer.

The function returns NULL, with the error "Invalid parameter for logarithm".

Note: The EXP() and LOG() methods are also useful.

Syntax:

Here is the syntax of MySQL LN() function:

LN(numeric value)

Parameter Values

Parameter Description
numeric value Required. A numeric value. value greater than 0.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: The natural logarithm of 5 is returned in this example:

SELECT LN(5);

Result:

1.6094379124341

The natural logarithm of 5 is returned as 1.6094379124341.

Example 2: The natural logarithm of 1 is returned in this example:

SELECT LN(1);

Result:

0

The natural logarithm of 1 is returned as 0.

Exmaple 3: Let's find out logarithm value of 0, -1, NULL from this example:

SELECT LN(0),
LN(-2),
LN(NULL);

Result:

LN(0) LN(-2) LN(NULL)
NULL NULL NULL

As a result, when you use 0, -1, and NULL values inside the LN() function, it will return NULL.


MySQL LOG() Function

The LOG() returns the natural logarithm of an integer with only one parameter.

You can calculate LOG() function in MySQL calculate the logarithm of an integer in a given base also with base parameter.

The function returns NULL if number is less than or equal to 0.0E0, and an error "Invalid parameter for logarithm" is reported.

Note: The EXP() function is the inverse of this function (when used with a single argument).

Syntax:

Here is the syntax of MySQL LOG() function:

LOG(numeric value)

or

LOG(base, numeric value)

Parameter Values

Parameter Description
numeric value Required. A number. should be greater than 0.
base The base of number. should be greater than 1.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: The natural logarithm of 5 is returned in this example:

SELECT LOG(1), 
LOG(5);

Result:

LOG(1) LOG(5)
0 1.6094379124341

The natural logarithm of 1 is 0, and the natural logarithm of 5 is returned as 1.6094379124341.

Example 2: To a given base, convert the natural logarithm of specified value:

SELECT LOG(EXP(1), 1),
LOG(EXP(1), 2),
LOG(4, 64),
LOG(100, 1000);

Result:

LOG(exp(1), 1) LOG(exp(1), 2) LOG(4, 64) LOG(100, 1000)
0 0.693147180559945 3 1.5

The result converts the natural logarithm of a specified value into a given base.

Example 3: This example to return NULL value using LOG() function:

SELECT LOG(0),
LOG(-1),
LOG(1, 10),
LOG(NULL);

Result:

LOG(0) LOG(-1) LOG(1, 10) LOG(NULL)
NULL NULL NULL NULL

There is no valid specified value in this result, so the result will be NULL. also if there is null in the parameter output is NULL.


MySQL MOD() Function

The MOD() method returns the residual of an integer divided by another number.

Note: The function is simple representation of number1 % number2.

Syntax:

Here is the syntax of MySQL MOD() function:

MOD(number1, number2)

or

number1 MOD number2

Parameter Values

Parameter Description
number1 Required. denominator.
number2 Required. The divisor

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: Example to return the remainder of 23 % 5:

SELECT MOD(23, 5);

Otherwise, You can use without MOD function:

SELECT 23 MOD 5;

Result:

3

The function returns the modulus of specific value.

Example 2: Example to return the remainder of 100 % 10:

SELECT MOD(100, 10);

Otherwise, You can use without MOD function:

SELECT 100 MOD 10;

Result:

0

The function returns the modulus of specific value.

Example 3:

SELECT MOD(1, 0), 
MOD(NULL, 1);

Result:

NULL

The function returns null, because parameter used 0 or NULL.


MySQL PI() Function

The PI() function returns the value of PI.

Although MySQL uses the full double-precision value internally, the default number of decimal places displayed is seven.

Note: The RADIANS() and DEGREES() methods are also useful.

Syntax:

Here is the syntax of MySQL PI() function:

PI()

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: Example to return the value of PI:

SELECT PI();

Result:

3.141593

The result give value of pi value.

Example 2: If you want more decimal places:

SELECT PI()+0.000000000000000000;

Result:

3.14159265358979

From the result, you can see that the user can specify decimal places. It will return the corresponding decimal spaces what you specified.


MySQL POW() Function

POW() returns the result of multiplying one number by the power of another number.

Note: This method is the same as POWER().

Syntax:

Here is the syntax of MySQL POW() function:

POW(x, y)

Parameter Values

Parameter Description
x Required. The base.
y Required. The exponent.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: Return 6 to the second power as an example:

SELECT POW(6, 2);

Result:

36

As per result the second power of 6 is 36.

Example 2: Return 4 to the third power as an example:

SELECT POW(4, 3);

Result:

64

As per result the third power of 4 is 64.

Example 3: Let's use fractional value and negative values in this example:

SELECT POW(2.5, 2),
POW(2, -2),
POW(2, -4);

Result:

POW(3.5, 2) POW(2, -3) POW(2, -4)
12.25 0.125 0.0625

From the result, find the power of the fractional value. and also using a negative number as a power to multiply one number.

Example 4: Using NULL value:

SELECT POW(2, NULL),
POW(NULL, 2),
POW(NULL, NULL);

Result:

POW(2, NULL) POW(NULL, 2) POW(NULL, NULL)
NULL NULL NULL

If there is a NULL in the parameter, it will return NULL.


MySQL POWER() Function

Main Article :- MySql POWER() Function

POWER() returns the result of multiplying one number by the power of another number.

Syntax:

Here is the syntax of MySQL POWER() function:

POWER(x, y)

Parameter Values

Parameter Description
x Required. The base.
y Required. The exponent.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: Return 6 to the second power as an example:

SELECT POWER(6, 2);

Result:

36

As per result the second power of 6 is 36.

Example 2: Return 4 to the third power as an example:

SELECT POWER(4, 3);

Result:

64

As per result the third power of 4 is 64.

Example 3: Let's use fractional value and negative values in this example:

SELECT POWER(2.5, 2),
POWER(2, -2),
POWER(2, -4);

Result:

POWER(3.5, 2) POWER(2, -3) POWER(2, -4)
12.25 0.125 0.0625

From the result, find the power of the fractional value. and also using a negative number as a power to multiply one number.

Example 4: Using NULL value:

SELECT POWER(2, NULL),
POWER(NULL, 2),
POWER(NULL, NULL);

Result:

POWER(2, NULL) POWER(NULL, 2) POWER(NULL, NULL)
NULL NULL NULL

If there is a NULL in the parameter, it will return NULL.


MySQL RADIANS() Function

The RADIANS() function in MySQL turns a degree angle into a radian angle that is substantially equal.

Note: The DEGREES() and PI() methods are also useful.

Remember: π radians equals 180 degrees.

Syntax:

Here is the syntax of MySQL RADIANS() function:

RADIANS(numeric value)

Parameter Values

Parameter Description
numeric value Required. A numeric value in degrees.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: To convert a degree value to radians, use the following formula:

SELECT RADIANS(0),
RADIANS(90),
RADIANS(180),
RADIANS(360);

Result:

RADIANS(0) RADIANS(90) RADIANS(180) RADIANS(360)
0 1.5707963267949 3.14159265358979 6.28318530717959

Within the function, the degree value will be transformed to radians.

Example 2: Let's use negative degree value and null :

SELECT RADIANS(-90),
RADIANS(-180),
RADIANS(-360),
RADIANS(NULL);

Result:

RADIANS(-90) RADIANS(-180) RADIANS(-360) RADIANS(NULL)
-1.5707963267949 -3.14159265358979 -6.28318530717959 NULL

The result shows that the function contains a negative degree value and NULL.


MySQL RAND() Function

Main Article :- MySql RAND() Function

The RAND() function in MySQL returns a positive-sign random decimal value larger than or equal to 0.0 (inclusive) and less than 1.0. (exclusive).

Refer the following formulas to return random numbers in other ranges (such as a and b):

  • A number between a and b at random: RAND() * (b - a) + a.
  • A integer between a and b at random: FLOOR(RAND() * (b - a + 1) + a).

Syntax:

Here is the syntax of MySQL RAND() function:

RAND(seed)

Parameter Values

Parameter Description
seed Optional. If seed is specified, It generates a sequence of random integers that can be repeated. It returns a completely random number if no seed is supplied.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: If there is no seed value, it returns a fully random decimal number between 0 and 1:

SELECT RAND() AS FirstRandomValue,
RAND() AS SecondRandomValue,
RAND() AS ThirdRandomValue;

Result:

FirstRandomValue SecondRandomValue ThirdRandomValue
0.497530815654929 0.504734902181416 0.0310820946759378

This function generates a random decimal value between 0 and 1. As you can see, the result contains random values.

Example 2: It returns a fully random decimal number with the seed value specified:

SELECT RAND(4);

Result:

0.15595286540310166

The random value will be generated based on seed value 4.

Example 3: Use first formula to get a random number between greater than or equal to 20 and less than 30:

SELECT RAND() * (30 - 20) + 20,
RAND() * (30 - 20) + 20,
RAND() * (30 - 20) + 20,
RAND() * (30 - 20) + 20;

Result:

RAND() * (30 - 20) + 20 RAND() * (30 - 20) + 20 RAND() * (30 - 20) + 20 RAND() * (30 - 20) + 20
23.6562976088899 20.2016154585421 20.0391847733773 29.5910777985967

This result used to generate random decimal number between 20 to 30.

Example 4: Use second formula to get a random number between greater than or equal to 7 and less than 15:

SELECT FLOOR(RAND()*(15-7+1)+7),
FLOOR(RAND()*(15-7+1)+7),
FLOOR(RAND()*(15-7+1)+7);

Result:

FLOOR(RAND()*(15-7+1)+7) FLOOR(RAND()*(15-7+1)+7)1 FLOOR(RAND()*(15-7+1)+7)2
15 12 7

This result used to generate random whole number between 7 to 15.


MySQL ROUND() Function

Main Article :- MySql ROUND() Function

The ROUND() method rounds a number to a specified number of decimal places.

ROUND() applies the following rules vary according to the type of the first argument:

The "round half away from zero" or "round toward nearest" rule is applied to exact-value numbers by ROUND():

If a value has a fractional part of .5, it is rounded up to the next integer if it is positive, or down to the next integer if it is negative. (In other words, it is not zero.) If the fractional part is less than .5, the value is rounded down to the next integer if positive, and up to the next integer if negative.

ROUND() follows the "round to closest even" rule on many systems, which means that a number with a fractional portion nearly halfway between two integers is rounded to the nearest even number.

Syntax:

Here is the syntax of MySQL ROUND() function:

ROUND(numeric value, fractions)

Parameter Values

Parameter Description
numeric value Required. The number that will be rounded.
fractions Optional. How many decimal places should a number be rounded to? If no value is specified, it returns an integer (no decimals).

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: Round the figure to two decimal places in this example:

SELECT ROUND(143.355, 2);

Result:

143.36

The result is to round the numeric value based on two decimal places.

Example 2: Round the figure to zero decimal places in this example:

SELECT ROUND(669.2706, 0);

Result:

669

The result is to round the numeric value based on 0, so that all decimal places will be removed.

Example 3: This example round numeric value based on negative fraction number:

SELECT ROUND(143.179, -1),
ROUND(157.179, -2);

Result:

ROUND(143.179, -1) ROUND(143.179, -2)
140 200

The function will return a whole number when you use -1. It takes 10 as a base, so it will be based on the last two values before the point. And like that, when you use -2, it takes 100 as a base, so it will be based on the last three values before the point.

Example 4: used to Round the Price column (to 1 decimal):

Let's took BOOKSIMPLE table to working on following query:

BookName Price
Getting Started With SQL 99.99
MySql Query Performance Tuning 175.132

The query will be like this,

SELECT BookName, 
Price, ROUND(Price, 1) AS RoundedPrice 
FROM BOOKSIMPLE;

Result:

BookName Price RoundedPrice
Getting Started With SQL 99.99 100
MySql Query Performance Tuning 175.132 175

The result is used to round the nearest whole number based on 1 decimal in the table column.


MySQL SIGN() Function

Depending on whether the argument is positive, zero, or negative, MySQL's SIGN() method returns 1, 0, or -1 as the sign of the parameter.

One of the following values will be returned by this function:

  • If number greater than 0, it returns 1
  • If number equal to 0, it returns 0
  • If number less than 0, it returns -1
  • If number is NULL, the function will return NULL

Syntax:

Here is the syntax of MySQL SIGN() function:

SIGN(numeric value)

Parameter Values

Parameter Description
numeric value Required. Return the sign to this number.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example: Return an example of how to find the sign of a number:

SELECT SIGN(123),
SIGN(123.123),
SIGN(-123),
SIGN(-123.123),
SIGN(0),
SIGN(NULL);

Result:

SIGN(143) SIGN(143.143) SIGN(-143) SIGN(-143.123) SIGN(0) SIGN(NULL)
1 1 -1 -1 0 NULL

From the above result, you can see that the signs of positive, negative, zero, and null values are returned.


MySQL SIN() Function

The SIN() function in MySQL returns the sine of the given integer.

If number is NULL, the function will return NULL.

Syntax:

Here is the syntax of MySQL SIN() function:

SIN(numeric value)

Parameter Values

Parameter Description
numeric value Required. A number.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: To find the sine of a number, use the following formula:

SELECT SIN(3), 
SIN(-2),
SIN(0);

Result:

SIN(3) SIN(-2)
0.141120008059867 -0.909297426825682 0

From the result, find the sine value of whole negative, postive number, and zero.

Example 2: Let's use positive and negative decimal value in this example:

SELECT SIN(2.5),
SIN(0.2),
SIN(-0.5),
SIN(-0.2);

Result:

SIN(2.5) SIN(0.2) SIN(-0.5) SIN(-0.2)
0.598472144103957 0.198669330795061 -0.479425538604203 -0.198669330795061

This result provides the sine value of a decimal number.

Example 3: Using NULL and PI() in SIN() function:

SELECT SIN(PI()),
SIN(NULL);

Result:

SIN(PI()) SIN(NULL)
1.22464679914735E-16 NULL

The result will produce the pi() function's corresponding sine value. If there is a null value in the function, the output will be NULL.


MySQL SQRT() Function

Main Article :- MySql SQRT() Function

The square root of an integer is returned by the SQRT() function.

Note: The number should be positive. If you use a negative value, it will return NULL.

Syntax:

Here is the syntax of MySQL SQRT() function:

SQRT(numeric number)

Parameter Values

Parameter Description
numeric value Required. A nonnegative number.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: This example returns a number's square root:

SELECT SQRT(36),
SELECT SQRT(100),
SELECT SQRT(105);

Result:

SQRT(36) SQRT(100) SQRT(105)
6 10 10.2469507659596

The result is used to calculate the square root of a given number.

Example 2: Let's try to use negative number and NULL:

SELECT SQRT(-5),
SQRT(NULL);

Result:

SQRT(-5) SQRT(NULL)
NULL NULL

You can see that if you give a negative or NULL value as a parameter, it will return NULL.


MySQL TAN() Function

The tangent of an integer is returned by the TAN() function.

Syntax:

Here is the syntax of MySQL TAN() function:

TAN(numeric value)

Parameter Values

Parameter Description
number Required. A numeric value

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: In this example used to calculate tangent value:

SELECT TAN(2.5),
TAN(0.2),
TAN(-0.5),
TAN(-0.2),
TAN(0);

Result:

TAN(2.5) TAN(0.2) TAN(-0.5) TAN(-0.2) TAN(0)
-0.74702229723866 0.202710035508673 -0.54630248984379 -0.202710035508673 0

As per the result, the TAN function was used to calculate tangent value of positive number, negative number, and 0.

Example 3: Using NULL and PI() in TAN() function:

SELECT TAN(PI()),
TAN(NULL);

Result:

TAN(PI()) TAN(NULL)
-1.22464679914735E-16 NULL

The result will produce the pi() function's corresponding tangent value. If there is a null value in the function, the output will be NULL.


MySQL TRUNCATE() Function

TRUNCATE() is a MySQL function that truncates numbers to a specified number of decimal places.

If decimal is zero, the result lacks a decimal point and a fractional part.

decimal can be negative, causing the value number's decimal digits left of the decimal point to become zero.

Note: This function also familiar to FLOOR(), CEIL(), CEILING(), and ROUND() functions.

Syntax:

Here is the syntax of MySQL TRUNCATE() function:

TRUNCATE(numeric value, fractions)

Parameter Values

Parameter Description
numeric value Required. The number to truncate.
fractions Required. The decimal value to truncate.

Technical Details

Works in: From MySQL 4.0

Examples:-

Example 1: Return a number with 2 decimal places truncated:

SELECT TRUNCATE(143.197, 1),
TRUNCATE(143.197, 4),
TRUNCATE(143.197, 0);

Result:

TRUNCATE(143.197, 1) TRUNCATE(143.197, 4) TRUNCATE(143.197, 0)
143.1 143.197 143

As per result truncate will return specified number of decimal places.

Example 2: Negative number inside TRUNCATE function:

SELECT TRUNCATE(143.197, -1),
TRUNCATE(143.197, -2);

Result:

truncate(143.197, -1) truncate(143.197, -2)
140 100

The function will return a whole number when you use -1. It takes 10 as a base, so it will be based on the last two values before the point. And like that, when you use -2, it takes 100 as a base, so it will be based on the last three values before the point.

Example 3: When the function use NUll value:

TRUNCATE(123.179, NULL);

Result:

NULL

If there is null value in TAN() function it will return NULL.