ABS() in SQL
Returns the absolute (positive) value of a number by removing the negative sign if present.
Syntax
Return type
ABS() Function Example
What is ABS() in SQL?
The ABS()
function in SQL returns the absolute (positive) value of a given number by removing its negative sign if present. It is commonly used in mathematical calculations, financial analysis, and data normalization to ensure that values remain non-negative. This function is supported across various database systems, including MySQL, PostgreSQL, SQL Server, and SQLite. Since ABS()
only affects negative numbers and leaves positive values unchanged, it is particularly useful for handling differences between values, distance calculations, and statistical computations where only magnitude matters.
Parameters:
number: The numeric value whose absolute value is to be calculated.
Example Use Cases:
-- Calculate distance between two points
-- Find largest deviation from average
-- Calculate absolute temperature difference
Notes:
Behavior: Returns positive value regardless of input sign; zero remains zero
Performance Considerations: Simple numeric operation, highly efficient
Version Info: Core mathematical function available in all major DBMS
Deprecated/Recommended Alternatives: None
Error Handling:
Error: Returns NULL if input is NULL
Recommendation: Use COALESCE if NULL handling needed
Supported Databases:
DBMS
Function / Syntax
Example
Behavior with NULL
MySQL
ABS(number)
ABS(-15.7)
Returns NULL
PostgreSQL
ABS(number)
ABS(-15.7)
Returns NULL
SQL Server
ABS(number)
ABS(-15.7)
Returns NULL
SQLite
ABS(number)
ABS(-15.7)
Returns NULL
BigQuery
ABS(number)
ABS(-15.7)
Returns NULL
Snowflake
ABS(number)
ABS(-15.7)
Returns NULL
Athena
ABS(number)
ABS(-15.7)
Returns NULL
Related Functions:
POWER()
Returns the result of raising a base number to a specified power (exponent). Also written as POW() in some databases.
SIGN()
Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.
ROUND()
Rounds a number to a specified number of decimal places or to the nearest integer.
CEIL()/CEILING()
Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.
SQRT()
Returns the square root of a non-negative number. The square root is the value that, when multiplied by itself, gives the number.