MySQL Numeric Functions

Published on October 16, 2024 by @mritxperts

MySQL provides a rich set of numeric functions that allow you to perform various mathematical operations and manipulations on numerical data. These functions are useful when working with calculations, statistical analysis, and data transformations in your MySQL databases. In this blog post, we’ll explore all the important numeric functions in MySQL, along with examples to help you understand how to use them effectively.

By ITXperts

Table of Contents

  1. ABS()
  2. CEIL() / CEILING()
  3. FLOOR()
  4. ROUND()
  5. TRUNCATE()
  6. MOD()
  7. POWER()
  8. SQRT()
  9. LOG()
  10. EXP()
  11. PI()
  12. SIN(), COS(), TAN()
  13. SIGN()
  14. RAND()

1. ABS() – Absolute Value

The ABS() function returns the absolute (non-negative) value of a number.

Syntax:

SELECT ABS(number);

Example:

SELECT ABS(-15); -- Returns 15

2. CEIL() / CEILING() – Round Up

CEIL() (or CEILING()) rounds a number up to the nearest integer.

Syntax:

SELECT CEIL(number);

Example:

SELECT CEIL(4.3); -- Returns 5
SELECT CEIL(-4.8); -- Returns -4

3. FLOOR() – Round Down

FLOOR() rounds a number down to the nearest integer.

Syntax:

SELECT FLOOR(number);

Example:

SELECT FLOOR(7.8); -- Returns 7
SELECT FLOOR(-3.2); -- Returns -4

4. ROUND() – Round to Nearest Value

ROUND() rounds a number to a specified number of decimal places. You can also use it to round to the nearest integer.

Syntax:

SELECT ROUND(number, decimal_places);

Example:

SELECT ROUND(3.14159, 2); -- Returns 3.14
SELECT ROUND(123.456, 0); -- Returns 123

5. TRUNCATE() – Truncate to Decimal Places

TRUNCATE() truncates a number to a specified number of decimal places without rounding.

Syntax:

SELECT TRUNCATE(number, decimal_places);

Example:

SELECT TRUNCATE(3.14159, 2); -- Returns 3.14
SELECT TRUNCATE(123.456, 0); -- Returns 123

6. MOD() – Modulus (Remainder)

MOD() returns the remainder of a division operation.

Syntax:

SELECT MOD(dividend, divisor);

Example:

SELECT MOD(10, 3); -- Returns 1
SELECT MOD(17, 4); -- Returns 1

7. POWER() – Exponentiation

POWER() raises a number to a specified power.

Syntax:

SELECT POWER(number, exponent);

Example:

SELECT POWER(2, 3); -- Returns 8 (2 raised to the power of 3)
SELECT POWER(5, 2); -- Returns 25

8. SQRT() – Square Root

SQRT() returns the square root of a number.

Syntax:

SELECT SQRT(number);

Example:

SELECT SQRT(16); -- Returns 4
SELECT SQRT(81); -- Returns 9

9. LOG() – Natural Logarithm

LOG() returns the natural logarithm (base e) of a number. You can also specify a different base for the logarithm.

Syntax:

SELECT LOG(number);
SELECT LOG(base, number);

Example:

SELECT LOG(2.718281828); -- Returns 1 (natural log of e)
SELECT LOG(10, 100); -- Returns 2 (log base 10 of 100)

10. EXP() – Exponential Function

EXP() returns e raised to the power of the given number.

Syntax:

SELECT EXP(number);

Example:

SELECT EXP(1); -- Returns 2.718281828 (e^1)
SELECT EXP(2); -- Returns 7.389056099 (e^2)

11. PI() – Value of Pi

PI() returns the value of Pi (π), which is approximately 3.141593.

Syntax:

SELECT PI();

Example:

SELECT PI(); -- Returns 3.141593

12. SIN(), COS(), TAN() – Trigonometric Functions

MySQL provides basic trigonometric functions like SIN() (sine), COS() (cosine), and TAN() (tangent).

Syntax:

SELECT SIN(number);
SELECT COS(number);
SELECT TAN(number);

Example:

SELECT SIN(PI()/2); -- Returns 1 (sine of 90 degrees)
SELECT COS(PI()); -- Returns -1 (cosine of 180 degrees)
SELECT TAN(0); -- Returns 0 (tangent of 0 degrees)

13. SIGN() – Sign of a Number

SIGN() returns the sign of a number as -1, 0, or 1, depending on whether the number is negative, zero, or positive.

Syntax:

SELECT SIGN(number);

Example:

SELECT SIGN(-10); -- Returns -1
SELECT SIGN(0); -- Returns 0
SELECT SIGN(25); -- Returns 1

14. RAND() – Generate a Random Number

RAND() generates a random floating-point number between 0 and 1. Optionally, you can provide a seed for repeatable random values.

Syntax:

SELECT RAND();
SELECT RAND(seed);

Example:

SELECT RAND(); -- Returns a random number (e.g., 0.3456)
SELECT RAND(100); -- Returns the same random number every time when using the same seed

Conclusion

These are the most commonly used numeric functions in MySQL. Each of these functions serves a different purpose, whether it’s rounding numbers, performing trigonometric calculations, generating random numbers, or calculating powers and logarithms. Mastering these functions will help you work efficiently with numerical data in MySQL.

For more advanced use cases or if you have any questions, feel free to reach out to us at ITXperts. Happy coding!