How to Use ABS Function in MySQL: Syntax and Examples

In MySQL, use the ABS() function to get the absolute value of a number, which means it returns the number without its sign. For example, ABS(-5) returns 5. This function works with integers and decimals.

Syntax

The ABS() function takes one numeric argument and returns its absolute value, which is the number without its sign.

• number: The numeric value (integer or decimal) you want to convert to its absolute value.

sql

ABS(number)

Example

This example shows how to use ABS() with positive, negative, and zero values in a SELECT statement.

sql

SELECT ABS(-10) AS abs_value1, ABS(15.5) AS abs_value2, ABS(0) AS abs_value3;

Output

+------------+------------+------------+
| abs_value1 | abs_value2 | abs_value3 |
+------------+------------+------------+
|         10 |       15.5 |          0 |
+------------+------------+------------+

Common Pitfalls

Common mistakes when using ABS() include:

• Passing non-numeric values, which causes errors or unexpected results.
• Expecting ABS() to work on strings without converting them to numbers first.
• Using ABS() on NULL values returns NULL, not zero.

Always ensure the input is numeric to avoid errors.

sql

/* Wrong: Passing a string without conversion */
SELECT ABS('abc') AS result_wrong;

/* Right: Convert string to number first */
SELECT ABS(CAST(' -20' AS SIGNED)) AS result_right;

Output

+--------------+
| result_wrong |
+--------------+
|         NULL |
+--------------+

+--------------+
| result_right |
+--------------+
|           20 |
+--------------+

Quick Reference

Function	Description	Example	Result
ABS(number)	Returns absolute value of number	ABS(-5)	5
ABS(0)	Returns zero if input is zero	ABS(0)	0
ABS(NULL)	Returns NULL if input is NULL	ABS(NULL)	NULL

Key Takeaways

Use ABS() to get the absolute value of any numeric input in MySQL.

ABS() returns NULL if the input is NULL, not zero.

Ensure inputs are numeric to avoid errors or unexpected NULL results.

ABS() works with both integers and decimal numbers.

You can use ABS() in SELECT queries, WHERE clauses, and expressions.