SQL Server ABS() Function

Summary: in this tutorial, you will learn how to use the SQL Server ABS() function to return the absolute value of a number.

SQL Server ABS() function syntax

The ABS() function returns the absolute (positive) values of a number. In other words, the ABS() function changes from a negative value to a positive value. It does not affect positive values or zero.

Here’s the syntax of the ABS() function:

ABS ( numeric_expression )Code language: SQL (Structured Query Language) (sql)

In this syntax, the numeric_expression is a number or an expression that you want to find the absolute value.

The ABS() function returns the positive number of the input value with the type depending on the type of the input value.

The following table shows the type of the input value with the corresponding type of the return value.

The ABS() function return null if the input value is NULL.

SQL Server ABS() function examples

Let’s explore some examples of using the ABS() function.

1) Basic SQL Server ABS() function example

The following example uses the ABS function to find the absolute values of negative numbers:

SELECT ABS(-10) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
-----------
10Code language: SQL (Structured Query Language) (sql)

2) Using ABS() function with NULL

The ABS() function will return NULL if the input value is NULL. For example:

SELECT ABS(NULL) result;Code language: SQL (Structured Query Language) (sql)

Output:

SELECT ABS(NULL) result;Code language: SQL (Structured Query Language) (sql)

3) Arithmetic overflow error example

When using the ABS() function, you may encouter an overflow error if you attempt to calculate the absolute value of a number that exceeds the maximum value representable by the specified type.

For example, the int data type in SQL Server has a value range from -2,147,483,648 to 2,147,483,647.

If you apply the the ABS() function to signed integer such as -2,147,483,648, you’ll encouter an overflow error because the absolute value exceeds the positive range limit for the int data type

The following snippet illustrates the arithmetic overflow error:

DECLARE @x INT;  
SET @x = -2147483648;  
SELECT ABS(@x);  
GO Code language: SQL (Structured Query Language) (sql)

Output:

Arithmetic overflow error converting expression to data type int.Code language: SQL (Structured Query Language) (sql)

4) Using the ABS function with table data

First, create a table called temperature_readings that store the temperature reading:

CREATE TABLE temperature_readings (
     id INT IDENTITY PRIMARY KEY,
     recorded_at DATETIME,
     temperature DECIMAL(5, 2)
);Code language: SQL (Structured Query Language) (sql)

Second, insert rows into the temperature_readings table:

INSERT INTO
  temperature_readings (recorded_at, temperature)
VALUES
  ('2024-04-04 08:00:00', 24.5),
  ('2024-04-04 08:15:00', 25.2),
  ('2024-04-04 08:30:00', 26.8),
  ('2024-04-04 08:45:00', 23.9),
  ('2024-04-04 09:00:00', 25.7);Code language: SQL (Structured Query Language) (sql)

Third, calculate absolute temperature differences from the reference temperature, which is 25 degrees Celsius:

SELECT
  recorded_at,
  temperature,
  ABS(temperature - 25) AS temperature_diff_from_ref
FROM
  temperature_readings;Code language: SQL (Structured Query Language) (sql)

Output:

recorded_at         | temperature | temperature_diff_from_ref
--------------------+-------------+--------------------------
2024-04-04 08:00:00 | 24.50       | 0.50
2024-04-04 08:15:00 | 25.20       | 0.20
2024-04-04 08:30:00 | 26.80       | 1.80
2024-04-04 08:45:00 | 23.90       | 1.10
2024-04-04 09:00:00 | 25.70       | 0.70
(5 rows)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the ABS() function to return the absolute value of a number.