SQL ABS Function

The SQL Server ABS function is a Mathematical Function that will return the absolute positive value of the specified numeric expression. In short, it changes or converts the negative numbers in a database table to positive. It is very helpful while analyzing the data, where the magnitude of the numbers is more important than the sign.

For instance, if you need to convert all the negative values into positive and leave the zeros and positive values as they are (untouched), use the SQL ABS() function. Because of this wonderful feature, you can use the ABS() function to find the difference between two points, distance, temperature, and other statistical computations. This function accepts any numeric data type, such as INT, FLOAT, DECIMAL, MONEY, etc.

SQL ABS Function Syntax

The syntax of the ABS Function to find the absolute value is:

SELECT ABS (Numeric_Expression)
FROM [Source]

SQL Server ABS Function Example

We can also use the WHERE clause to define a condition for filtering the data. The following are some key points to remember when working with the ABS() function.

  • If the Numeric_Expression is negative, it returns an equivalent positive value.
  • If it is Positive or Zero, it returns the same value (untouched).
  • If the numeric value is NULL, the ABS() function will return a NULL value.
  • The data type of the return value is the same as the given numeric expression.

NOTE: Although some people think both the ABS and SIGN functions are the same, there is a major difference between them. The SIGN() function returns the values -1, 0, and 1 for negative, zero, and positive values. Whereas the ABS() function converts negative to positive, focus on the magnitude.

SQL Server ABS Function Example

The ABS Function returns the absolute positive value of any numeric value or number. The following Mathematical Function query will show multiple ways to use the ABS function.

The following SELECT statement applies the ABS() function directly on a positive value.

SELECT ABS(27) AS PosVal

Output:

PosVal
------
27

The statement below finds the absolute positive value of the negative number.

SELECT ABS(-27) AS NegVal

Output:

NegVal
-------
27

In this SQL Server statement, we use zero as the argument. As we all know, it returns zero.

SELECT ABS(0) AS Zero;

Result:

Zero
----
0

Apart from the above, we can use the SQL ABS() function by passing mathematical expressions. For instance, the code below uses multiple values and performs addition and subtraction. Next, the function returns the positive of the final value.

SELECT ABS(2 + 55 - 77) AS MathCal

It calculates (2 + 55 – 77) = -20. The absolute positive value of -20 is 20.

MathCal
-----
20

The query below shows how to find the absolute positive value of floating-point values using the ABS() function.

SELECT ABS(-7.88) AS DecVal

Result:

DecVal
----
7.88

Here, we used the ABS function to find the absolute value of the variable @i. It means ABS(-250.89765) and is assigned a new name, ‘Absolute Value’, using the ALIAS Column.

DECLARE @i INT
SET @i = -250.89765

SELECT ABS(@i) AS [Absolute Value]

Output

250

Along with the above-mentioned options, you can use this function on variables. Here, we declared a variable @i of INTEGER type, and used the ABS function to find the absolute value of it. Next, we applied the ROUND() to cut the decimal precision to 3.

DECLARE @i FLOAT
SET @i = -250.89765

SELECT ROUND(ABS(@i), 3) AS [Absolute Value]

Output:

Absolute Value
------
250.898

TIP: If you replace the FLOAT data type with INT and keep the value as -250.89765, the output will be converted to an integer and return 250.

NULL Values Example

As we mentioned earlier, if you pass a NULL as an argument value, the SQL ABS() function will return NULL as the result set.

SELECT ABS(NULL) AS NL

SELECT ABS(NULL + 50) AS NL2

Result:

NULL

NULL

The second statement also returns NULL because anything added to NULL is also a NULL. Please refer to the Arithmetic Operators for more information. To handle the NULL values, use the COALESCE function.

String Values Example

If we pass any information enclosed within ‘ ‘ and the server can implicitly convert them as a numeric value, the SQL Server ABS() function will return the positive value of the given. The following query converts the string value to a numeric and returns the Absolute positive value.

SELECT ABS('-10') AS txtNum

It considers it as the number and returns the result.

txtNum
----
10

If you pass a string of information or text that can’t be converted to a numeric value, it will throw an error. For instance, the code below returns an error.

SELECT ABS('Tutorial') AS text

The Error thrown by the above query is:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

SQL ABS Function On Tables

The ABS Function also allows you to find the absolute values of column values. Imagine a table containing the positive and negative numeric values of some statistical information, or calculating the absolute difference between two columns, use the ABS() function on them. To demonstrate this example, we use the AdventureWork2022 SalesTerritory table data.

Here, we will find the regular and the absolute difference between the sales this year and the last year in each territory. For this, we wrap the subtraction process within the ABS() function.

SELECT [TerritoryID],
    [Name],
    [Group],
    [SalesYTD],
    [SalesLastYear],
    SalesYTD - SalesLastYear AS Diff,
    ABS(SalesYTD - SalesLastYear) AS AbsoluteDiff
FROM [Sales].[SalesTerritory]
SQL ABS Function to find Absolute Positive Value

Distance Between Two Points

Similarly, you can use the combination of the ABS, SQRT, and the POWER() table to find the distance between two points. To make it simple and understandable, we used the salesperson table to find the difference between the sales quota and achieved.

SELECT [BusinessEntityID]
      ,[SalesQuota]
      ,[SalesYTD]
      ,[SalesLastYear]
      ,[SalesYTD] - [SalesLastYear] AS SaleDiff
      ,SalesQuota -SalesYTD  AS QDiff
      ,ABS(SQRT(POWER(SalesYTD - SalesQuota, 2))) AS AbsoluteDistance
  FROM [Sales].[SalesPerson]

ERROR: Arithmetic Overflow

While working with the SQL ABS() function, you must be careful with the upper and lower limits of any numeric data type. Because when you try to find the absolute value of any number that exceeds the upper limit, an arithmetic overflow error will appear.

For instance, the lower and upper limits of the signed TINYINT are -128 to 127. Consider this case: if you try to find the absolute positive value of the lower limit, i.e., -128, the ABS() function will return an Arithmetic overflow error. Because the upper limit of TINYINT is 127, and 128 exceeds that boundary.

DECLARE @n TINYINT = -128
SELECT ABS(@n) AS RS

The above statement will throw the following error.

Msg 220, Level 16, State 2, Line 4
Arithmetic overflow error for data type tinyint, value = -128.
Categories SQL