Back to Library

ABS() in SQL

Returns the absolute (positive) value of a number by removing the negative sign if present.

Syntax

ABS(number)
Return type
Returns the same data type as input (NUMERIC, FLOAT, INTEGER, etc.)

ABS() Function Example

SELECT ABS(-123.45); 
-- Output: 123.45

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

SELECT ABS(end_point - start_point) AS distance FROM measurements;
-- Output:
distance
--------
15
23
8
42
31

-- Find largest deviation from average

SELECT ABS(value - avg_value) AS deviation FROM statistics;


-- Output:
deviation
---------
4.7
2.3
8.1
1.5
5.9

 -- Calculate absolute temperature difference 

SELECT ABS(actual_temp - target_temp) AS temp_difference FROM readings;
-- Output:
temp_difference
--------------
2.5
3.8
1.2
4.6
2.9

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:

ABS()

Returns the absolute (positive) value of a number by removing the negative sign if present.

Returns the absolute (positive) value of a number by removing the negative sign if present.

Returns the absolute (positive) value of a number by removing the negative sign if present.

Returns the absolute (positive) value of a number by removing the negative sign if present.

RAND()/RANDOM()

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

Generates a random number. Without parameters, returns a floating-point number between 0 (inclusive) and 1 (exclusive). Some DBMS allow seeding for reproducible results.

MOD()

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

Returns the remainder after dividing one number by another. Also available as % operator in many databases.

FLOOR()

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.

One place for all your queries,
directly on your SQL editor