MariaDB Numieric Funcitons

Numeric functions in MariaDB allow you to manipulate the numeric values. They are also called as mathematical functions.

1. ABS Function

Syntax:

 ABS(arg);

This function returns the absolute value of the argument("arg") provided, i.e., non-negative value. If the argument value is not numeric, then it is converted to a numeric type.

For example: 

1. Returns positive value:

MariaDB [(none)]> SELECT ABS (-400);

2. MariaDB [(none)]> SELECT ABS (-400.6);

3. When we pass an absolute value itself, it returns the same value:

.MariaDB [(none)]> SELECT ABS (6);

4. When we pass multiply operation with one operand negative and another as positive, the result is a positive integer because of ABS().

MariaDB [(none)]> SELECT ABS(12* -2);

5. When we pass a DATE type argument, the result is continuous integer excluding the signs

MariaDB [(none)]> SELECT ABS(DATE '1999-08-13');

2. ACOS Function

Syntax:

ACOS(arg);

This Function will return the arc cosine of the argument(arg), i.e., the value whose cosine is arg. Note that if the argument provided is not within the range of -1 to 1, this function returns NULL.

For example:

1. Positive value is passed as an argument, which will return the arc cosine of it

MariaDB [(none)]> SELECT ACOS(1);

2. Let's pass an argument above the range, i.e., 1.0001, which is greater than 1

MariaDB [(none)]> SELECT ACOS(1.0001);

Here we can see NULL is returned.

3.    Lets pass a negative decimal value:

MariaDB [(none)]> SELECT ACOS(-0.5);

3. ASIN Function

Syntax:

ASIN(arg);

This Function will return the arc sine of the argument(arg), i.e., the value whose sine is the argument provided. Note that if the argument is in the range -1 to 1, then the function returns NULL.

For example:

1. We will be passing a positive value that is within the range

MariaDB [(none)]> SELECT ASIN(0.21);

2. Next, a negative value within range:

MariaDB [(none)]> SELECT ASIN(-0.1);

3. Further, the value that does not fall within range:

MariaDB [(none)]> SELECT ASIN(1.1);

Here we can the result is NULL.

4. Finally, we will pass a string datatype argument

MariaDB [(none)]> SELECT ASIN('MOO');

Here we can see the result is 0, and a warning is generated.

Using the SHOW WARNING command, we can access the warnings generated by MariaDB. We cannot pass any varchar or string type values. The result generated will be '0', and the warning will be generated.

4. ATAN Function

This Function will return the arctangent of the argument(arg), i.e., the value whose tangent is the argument provided.

There are two ways to use this function as follows:

ATAN (argument)
OR
ATAN(x,y)

The argument is the number we use to evaluate arctangent.

The two values, x, and y are the values used to evaluate the arctangent

Note that The sign of x and y used in the second syntax is utilized to find the quadrant for the resultant value.

For example:

MariaDB [(none)]> SELECT ATAN(2);

5. ATAN2 Function

Syntax:

ATAN2(X,Y)

This function returns the arctangent of the two values provided - X and Y

The result is equivalent to the calculation of arc tangent of X/Y, excluding the signs of both the values are utilized to identify the quadrant of the result set

For example:

1. MariaDB [(none)]> SELECT ATAN(1,3);

2. We can also pass the PI() Function as an argument.

MariaDB [(none)]> SELECT ATAN(PI(), 3);

3. We can also pass a negative value

MariaDB[(none)]> SELECT ATAN(-1.3,2);

6. CEIL Function

Syntax:

 CEIL(arg)

 This function returns the smallest value, which is not less than an argument.

Note that CEIL function is equivalent to the CEILING Function.

For example:

1. We will pass a positive value that has a decimal point. It will return the smallest value after the argument.

MariaDB [(none)]> SELECT CEIL(12.1);

2. CEILING() function returns same result as of CEIL() function. In the following example, we have passed a negative value.

MariaDB [(none)]> SELECT CEILING(-1.23);

3. MariaDB [(none)]> SELECT CEILING(1.23);

7. COS Function

Syntax:

COS (arg)

This function returns the cosine of the argument(arg) provided. The arg is in the radian format.

For example:

1. MariaDB [(none)]> SELECT COS(-0.4);

2.We can also pass a function. Here we are passing the PI().

MariaDB [(none)]> SELECT COS(PI());

8. COT Function

Syntax:

COT(arg)

This Function will return the cotangent of the argument(arg).

Note that if the argument is 0, then the function will return an error.

For example:

1. In the following query, we are passing a negative value

MariaDB [(none)]> SELECT COT(-2);

2. We can also get a cos of positive integer.

MariaDB [(none)]> SELECT COT(4);

9. DEGRESS Function

Syntax:

DEGREES(arg)

This Function will return the argument arg, which is converted to degrees specified in radian form.

For example:

1.In the following statement, we pass a radian value, i.e., PI(), so the result is in degrees form.

MariaDB [(none)]> SELECT DEGREES(PI());

In the above example, PI() returns a 180-degree value.

2. Here, we are multiplying the PI() value with two, which doubles the angle, so the value returned is 360 degrees

MariaDB [(none)]> SELECT DEGREES(PI()*2);

3. Next, we will pass a radian value as 1 lets see the degree value :

MariaDB [(none)]> SELECT DEGREES(1);

10. DIV Function

Syntax:

X DIV Y

This Function is used for integer division wherein X is the value divided by Y and the answer the returned.

This Function is equivalent to FLOOR() but is secure when we use BIGINT. When we use non-integer operands that surpass the BIGINT limit, the result returned would be wrong.

The result is the quotient of the Division's operation. If we want to get the remainder, we can use the MOD operator

For Example:

1. We will pass a whole number.

MariaDB [(none)]> SELECT 81 DIV 9;

2. Next we will pass some decimal values

MariaDB [(none)]> SELECT 12.6 DIV 3.4;

3. Further we will see an example of negative value as argument

MariaDB [(none)]> SELECT 18.4 DIV -2.5;

4. We are not allowed to pass value as 0 or it will return NULL

MariaDB [(none)]> SELECT 81 DIV 0;

We can see a warning is generated. Using SHOW WARNINGS, we can see the warning here. We can see the warning says Division by 0.

11. EXP() Function

Syntax:

 EXP(arg)

This function will return the value of e i.e the base of natural logarithms raise to the power of arg i.e e^arg. The LOG() function is the inverse of this function and also of LN()

For example:

1. MariaDB [(none)]> SELECT EXP(-1);

2. MariaDB [(none)]> SELECT EXP (2);

3. MariaDB [(none)]> SELECT EXP(0);

4. Lets see the result of NULL

MariaDB [(none)]> SELECT EXP (NULL);

12. FLOOR() Function

Syntax:

FLOOR(arg)

This function will return the largest value, which is equal or is not greater than arg.

For Example:

1.MariaDB (none)]> SELECT FLOOR(1.55);

Here we can see the 1 is returned as it is the largest value which is less than 1.55

2. Lets see some negative value

 MariaDB [(none)]> SELECT FLOOR (-1.55);

3. If we pass a whole number is returns the same.

 MariaDB [(none)]> SELECT FLOOR (12);

4. MariaDB [(none)]> SELECT FLOOR (12.7);

13. MariaDB- GREATEST() Function

Syntax:

GREATEST(arg_1, arg_2,..);

This function will return the largest argument i.e maximum value from both the arguments(arg_1 & arg_2).

If the arguments are numeric values, this function will return the largest integer value.

If the arguments are string values, this function will return the largest string value.

For example:

1. In the first example, let us pass on two arguments. Here the greatest value will be returned.

MariaDB [(none)]> SELECT GREATEST (2,7);

2. We can also pass decimal numbers; the greatest of them will be returned.

MariaDB [(none)]> SELECT GREATEST (34.5,378.0,34.7);

3. Next, We will pass characters; the one with the alphabetically largest value will be returned.

4. Next, We will be passing strings and the will be compared based on the first letter of the string.

5. Finally, we will pass a NULL value with strings. It will return NULL as it is greater.

MariaDB [(none)]> SELECT GREATEST(INFORMATION", "TECHNOLOGY', 'ANIMATION,NULL);

14. LEAST() Function

Syntax:

LEAST(arg_1, arg_2,..arg_n);

This function will return the smallest argument i.e minimum value from both the arguments(arg_1 & arg_2).

If the arguments are numeric values, this function will return the smallest integer value.

If the arguments are case-sensitive string values, the arguments are compared as case-sensitive; this Function will return the smallest string value.

For other instances, the arguments are differentiated as case Insensitive string

If the argument is NULL, LEAST() will return NULL.

For example:

1. We will pass two arguments; the smallest integer will be returned

MariaDB [(none)]> SELECT LEAST (2,7);

2. Next, We will pass three decimal numbers as arguments

MariaDB [(none)]> SELECT LEAST (34.5,378.0,34.7);

3. Next let’s see characters.

MariaDB [(none)]> SELECT LEAST ('B', 'A', 'C');

4. Next, the strings

MariaDB [(none)]> SELECT LEAST ('INFORMATION','TECHNOLOGY', 'ANIMATION");

5. Finally with the NULL and strings.

MariaDB [(none)]> SELECT LEAST ('INFORMATION, TECHNOLOGY", "ANIMATION",NULL);

15. LN() Function

Syntax:

LN(arg)

This Function will return the natural logarithm of the argument, i.e., the base-e logarithm of the argument. The argument should be greater than 0. If it is equal to or less than 0 , NULL will be returned.

This Function is the inverse of EXP().

For example:

1. We will be passing a positive number. Let's see the result.

MariaDB [(none)]> SELECT LN(2);

2. Lets pass negative value.

MariaDB [(none)]> SELECT LN(-2);

As we can see range allowed is above 0 so any negative value passed will return a NULL and a warning.

Using SHOW WARNING we can see the warnings.

3. Next we will pass 0 as arguments.

MariaDB [(none)]> SELECT LN(0);

16. LOG() Function

Syntax:

LOG(arg) 
OR 
LOG(B,arg)

There are two ways to specify arguments:

If we mention only one argument, then LOG() will return the natural logarithm of the arg. The arg should be greater than 0; If it is less than or equal to 0, then NULL will be returned.

If we mention two arguments, LOG() will return the logarithm of arg to the BASE B. B should be greater than 1, and arg should be greater than 0; otherwise, it will return NULL.

If the arg is NULL, the result will also be NULL

The inverse of this Function is the EXP().

For example:

1. MariaDB [(none)]> SELECT LOG(2);

2. Negative value which returns NULL and a waring

MariaDB [(none)]> SELECT LOG(-2);

3. Next, Argument will be a 0.

MariaDB [(none)]> SELECT LOG(0);

Lets see examples with two arguments:

1. MariaDB [(none)]> SELECT LOG(2,16);

2. The second argument should be greater than 0 lets see the result

MariaDB [(none)]> SELECT LOG(4,0);

We can see NULL is returned as well as a Warning.

3. Next , we will pass first arg as 1,By rules it should be greater than 1.

MariaDB [(none)]> SELECT LOG(4,0);

Here we can see NULL and Warning is returned.

17. LOG10() Function

Syntax:

LOG10(arg);

This Function will return the base-10 logarithm of arg. arg should be greater than 0; otherwise, Function will return NULL

For example:

1. MariaDB [(none)]> SELECT LOG18 (4);
2. MariaDB [(none)]> SELECT LOG10 (100);

2. Next, Negative value:

MariaDB [(none)]> SELECT LOG10(-100);

Here we can see NULL and Warning is returned.

3. Next, argument as 0.

MariaDB [(none)]> SELECT LOG10(0);

Here we can see NULL and Warning is returned.

4. Lets pass two arguments.

MariaDB [(none)]>SELECT LOG10 (3.55);

18. LOG2() Function

Syntax:

LOG2(arg);

This Function will return the base-2 logarithm of arg. arg should be greater than 0; otherwise, Function will return NULL

For example:

1. Example with a positive argument

MariaDB[(none)]> SELECT LOG2 (4);

2. Next, example with a positive decimal number.

MariaDB [(none)]> SELECT LOG2(4.24);

3. Next, argument will be 0 which will return NULL and warning.

MariaDB (none) > SELECT LOG2(0);

4. Finally a negative value which behaves same as 0.

MariaDB [(none)]> SELECT LOG2(-4);

19. MOD() Function

There are 3 different ways we can use this function follows:

Syntax:

MOD(X,Y);
Or
X MOD Y;
Or
X % Y

This Function will return the remainder of X divided by Y.This Function uses the following formula - X/Y. This Function returns the remainder without any rounding.

For example:

1. Lets see example for first syntax:

MariaDB [(none)]> SELECT MOD(7,3);

2. Next, we will pass one of the argument as decimal.

MariaDB [(none)]> SELECT MOD(7,4.3);

3. Next, with the second syntax and same arguments

MariaDB [(none)]> SELECT MOD(7,4.3);

4. Next, with the third syntax.

MariaDB [(none)]> SELECT 7 % 3;

2. And Finally, with the decimal argument.

MariaDB [(none)]> SELECT 7 % 4.3;

20. PI() Function

Syntax:

PI();

This function will return the pi(p) value, and the answer is displayed with six decimal places. No arguments are required.

For example:

1. We can see the traditional value of pi.

MariaDB [(none)]> SELECT PI();

2. We can use other operators with the function.

MariaDB [(none)]> SELECT PI()*2;

3. MariaDB [(none)]> SELECT PI()+0.0000000000;

21. POW() Function

Syntax:

POW(X, Y);

This function returns the value of X raised to the Yth power.

This Function is equivalent to the POWER() Function.

For example:

1. MariaDB [(none)]> SELECT POW(3,2);

2. Negative number

MariaDB [(none)]> SELECT POW(3,-2);

3. Decimal numbers

MariaDB [(none)]> SELECT POW(2.3,3);

4. MariaDB [(none)]> SELECT POW(0,4);

22. RADIANS() Function

Syntax:

RADIANS(arg);

This function returns the argument(arg) converted from degrees to radians.

Note that p radians equal 180 degrees. This Function is the converse of the DEGREES() Function.

For Example

MariaDB [(none)]> SELECT RADIANS(90);

We can also use PI() function as argument

MariaDB [(none)]> SELECT RADIANS(PI());

23. MariaDB- RAND() Function

Syntax:

RAND() 
OR
RAND(x);

This Function will return the random double-precision floating-point value n, which falls in the range 0 to 1.0.

If we pass an argument x, it is treated as a seed value, a repeatable sequence of column values is generated. If we pass the same x value, it will generate the same sequence every time.

For Example:

1. We will use the first form of RAND() i.e. without argument.

MariaDB [(none)]> SELECT RAND();

2. Next, with the SEED value.

MariaDB [(none)]> SELECT RAND(8);

3. Next, we will create a table and insert some values, and we will see the same sequence is returned if we pass the same SEED value.

We have created a table named TAB, and we have inserted 3 integer values 1,2, and 3.

First, we will use RAND() without any argument.

MariaDB [student]> select i, RAND() from tab;

Next we will use function with argument and see if we pass it again the same sequence is returned next time.

RANDOM DECIMAL RANGE

There are formulas to generate the random decimal range:

SELECT RAND()*(b-a)+a;

 Where is smallest, and b is the largest number from the range.

For example:

MariaDB [student]> SELECT RAND()*(9-2)+1;

Next, RAND() with the argument.

RANDOM INTEGER RANGE

To generate integer numbers from the range, we can use the following formula:

SELECT FLOOR(RAND()*(b-a+1))+a;

For example:

MariaDB [student]> SELECT FLOOR(RAND()*75-50+1)+40;

Next, with the argument.

MariaDB [student]> SELECT FLOOR(RAND(8)*75-50+1)+40;

24. ROUND() Function

There are two ways to use ROUND() function as follows:

Syntax:

ROUND(X)
OR
ROUND(X, DEC);

This function will return a number to a certain number of decimal places. The rounding algorithm depends on the data type of X. If we don’t mention the DEC, by default it is identified as 0. We can also specify DEC as a negative value which leads to DEC digits left of decimal point of the X to become zero.

For Example:

MariaDB [student]> SELECT ROUND(-1.22);

MariaDB [student]> SELECT ROUND(2.299, 1);

25. MariaDB- SIGN() Function

Syntax:

SIGN(arg);

This function will return the value representing the sign of the argument.

If the argument is positive, the result is 1.

If the argument is negative, the result is -1.

If the argument is 0, the result is 0.

For example:

1. First, Let's see for a positive number.

MariaDB [student]> SELECT SIGN(30);

2 Next, For negative value.

MariaDB [student]> SELECT SIGN(-30);

3. And finally for 0

MariaDB [student]> SELECT SIGN(0);

26. SIN() Function

Syntax:

SIN(arg);

This function returns the sine of arg, where arg provided is in radians form.

For Example:

1. MariaDB [(none)]> SELECT SIN(1);

2. We can also pass a function like PI()

MariaDB [(none)]> SELECT SIN(PI());

3. MariaDB [(none)]> SELECT ROUND(SIN(PI()));

27. SQRT () Function

Syntax:

SQRT(arg);

This function will return the square root of arg. If we provide a negative value, the result returned would be NULL

For example:

1. MariaDB [(none)]> SELECT SQRT(64);

MariaDB [(none)]> SELECT SQRT(0);

28. TAN() Function

Syntax:

TAN(arg)

This function returns a tangent of arg, Where the arg provided is radian in form.

For example:

MariaDB [(none)]> SELECT TAN(1);

MariaDB [(none)]> SELECT TAN(PI());