SQL Server ABS() Function

In SQL Server, ABS() returns the absolute value of a numeric expression: negative inputs become nonnegative, while zero and positive inputs are unchanged. The result type depends on the input type. See Microsoft’s ABS() reference.

Syntax

ABS(numeric_expression)

numeric_expression: an expression of an exact or approximate numeric type.

Return type

The return type depends on the input; ABS() does not always return an integer. SQL Server raises an arithmetic overflow error if the absolute value does not fit in the return type.

Examples

Example 1: Calculating the Absolute Value of a Negative Number

Suppose we have a table containing numerical data, including positive and negative numbers. We want to calculate the absolute value of all negative numbers. The following SQL statement can be used:

SELECT ABS(value) as absolute_value
FROM my_table
WHERE value < 0;

Assuming the table data is as follows:

Running the above SQL statement will return the following result:

Example 2: Calculating the Distance Between Two Points

Suppose we have a table containing x and y coordinate data, with each row representing the coordinates of a point. We want to calculate the distance between the points. The following SQL statement can be used:

SELECT SQRT(
    POWER(CONVERT(float, x1) - CONVERT(float, x2), 2) +
    POWER(CONVERT(float, y1) - CONVERT(float, y2), 2)
) AS distance
FROM my_table;

In T-SQL, ^ is bitwise exclusive OR, not exponentiation; use POWER() for powers.

Assuming the table data is as follows:

Running the above SQL statement will return the following result:

Avoid overflow for the minimum integer

The most negative value of a signed integer type has no positive counterpart in the same type. For example, int ranges from -2,147,483,648 through 2,147,483,647, so this call raises Msg 8115:

DECLARE @minimum_int int = -2147483648;

SELECT ABS(@minimum_int);

Widen the expression before calling ABS() if the positive result must fit:

DECLARE @minimum_int int = -2147483648;

SELECT ABS(CONVERT(bigint, @minimum_int)) AS absolute_value;

This returns 2147483648. See SQL Server Error 8115: Arithmetic Overflow and the int range reference.

Conclusion

ABS() returns a nonnegative value using a return type determined by its input. Check the type’s positive range when the input can contain its minimum signed value.