How to use MySQL8 ABS() Math functions to get positive values?
In MySQL, we are provided with many inbuilt maths functions. They help us in doing complex calculations very easily. Now let’s start with these.
ABS() is a mathematical function. It returns the absolute value of the input string. Absolute means positive value. It will never return a negative value.
It returns NULL, if NULL is passed as an input string.
It returns Warning, if any string argument is passed.
This function is safe to use with BIGINT values.
The data type of the return value is similar to the data type of the input argument.
MySQL ABS() : Syntax
MySQL ABS() : Parameter
Name, Required /Optional,Type, Description
number, Required, Integer, It represents a valid number.
MySQL ABS() : Output
Return, Description
NULL, if the argument is NULL.
positive value, if the input number is negative number.
no effect, if the input number is positive number or zero.
MySQL ABS() Available from : MySQL 4.0
ABS() Example 1 : returns a positive number, if the input is a non-negative number.
A positive number will be returned when a non-negative number is passed as input. See the below example.
mysql> SELECT ABS(-12345.9586231); +---------------------+ | ABS(-12345.9586231) | +---------------------+ | 12345.9586231 | +---------------------+ 1 row in set (0.12 sec)
A positive number will be returned when a positive number is passed as input. See the below example.
mysql> SELECT ABS(12345.9586231); +--------------------+ | ABS(12345.9586231) | +--------------------+ | 12345.9586231 | +--------------------+ 1 row in set (0.00 sec)
STRCMP() Example 2 : returns ERROR 1690 (22003): BIGINT value is out of range in ‘abs(-(9223372036854775808))
Below is the example fo same.
mysql> SELECT ABS(-9223372036854775808); ERROR 1690 (22003): BIGINT value is out of range in 'abs(-(9223372036854775808))
ABS() Example 3 : return Warning if any string argument is passed.
See the below example :
mysql> SELECT ABS('testing');
+----------------+
| ABS('testing') |
+----------------+
| 0 |
+----------------+
1 row in set, 1 warning (0.06 sec)
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'abc' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)
ABS() Example 4 : NULL arguments
If the arguments are NULL, it will return NULL. See the below example :
mysql> SELECT ABS(NULL); +-----------+ | ABS(NULL) | +-----------+ | NULL | +-----------+ 1 row in set (0.00 sec)
ABS() Example 5 : Using expressions
Here are some more examples to use the expression with the ABS() function :
mysql> SELECT ABS(5+6); +----------+ | ABS(5+6) | +----------+ | 11 | +----------+ 1 row in set (0.06 sec) mysql> SELECT ABS(-5-6); +-----------+ | ABS(-5-6) | +-----------+ | 11 | +-----------+ 1 row in set (0.05 sec) mysql> SELECT ABS(15-6); +-----------+ | ABS(15-6) | +-----------+ | 9 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT ABS(-15-6); +------------+ | ABS(-15-6) | +------------+ | 21 | +------------+ 1 row in set (0.00 sec)
You can see in the below example, how the output sign gets changed without the use of ABS() function.
mysql> SELECT (-15-6); +---------+ | (-15-6) | +---------+ | -21 | +---------+ 1 row in set (0.00 sec)
mysql> SELECT ABS(5*6); +----------+ | ABS(5*6) | +----------+ | 30 | +----------+ 1 row in set (0.00 sec) mysql> SELECT ABS(-5*6); +-----------+ | ABS(-5*6) | +-----------+ | 30 | +-----------+ 1 row in set (0.00 sec)
Notice the difference between the above example and the below example.
mysql> SELECT (-5*6); +--------+ | (-5*6) | +--------+ | -30 | +--------+ 1 row in set (0.00 sec)
You can see the difference in the sign of the value. Without ABS() its showing -30 value.
