{"id":3715,"date":"2024-04-04T09:53:09","date_gmt":"2024-04-04T02:53:09","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=3715"},"modified":"2024-04-04T09:53:41","modified_gmt":"2024-04-04T02:53:41","slug":"sql-server-abs","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-math-functions\/sql-server-abs\/","title":{"rendered":"SQL Server ABS() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server ABS()<\/code> function to return the absolute value of a number.<\/p>\n\n\n\n

SQL Server ABS() function syntax #<\/a><\/h2>\n\n\n\n

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

Here’s the syntax of the ABS()<\/code> function:<\/p>\n\n\n

ABS ( numeric_expression )<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the numeric_expression<\/code> is a number or an expression that you want to find the absolute value.<\/p>\n\n\n\n
The ABS()<\/code> function returns the positive number of the input value with the type depending on the type of the input value. <\/p>\n\n\n\n
The following table shows the type of the input value with the corresponding type of the return value.<\/p>\n\n\n\n
Input type<\/th>Return type<\/th><\/tr><\/thead>
float, <\/strong>real<\/td>float<\/td><\/tr>
decimal(p, s)<\/td>decimal(38, s)<\/td><\/tr>
int, <\/strong>smallint, <\/strong>tinyint<\/td>int<\/td><\/tr>
bigint<\/td>bigint<\/td><\/tr>
money, smallmoney<\/td>money<\/td><\/tr>
bit<\/td>float<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The ABS()<\/code> function return null if the input value is NULL<\/code>.<\/p>\n\n\n\n
SQL Server ABS() function examples #<\/a><\/h2>\n\n\n\n
Let’s explore some examples of using the ABS()<\/code> function.<\/p>\n\n\n\n
1) Basic SQL Server ABS() function example #<\/a><\/h3>\n\n\n\n
The following example uses the ABS<\/code> function to find the absolute values of negative numbers:<\/p>\n\n\n
SELECT<\/span> ABS<\/span>(-10<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
result\n-----------<\/span>\n10<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using ABS() function with NULL #<\/a><\/h3>\n\n\n\n
The ABS()<\/code> function will return NULL<\/code> if the input value is NULL<\/code>. For example:<\/p>\n\n\n
SELECT<\/span> ABS<\/span>(NULL<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
SELECT<\/span> ABS<\/span>(NULL<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Arithmetic overflow error example #<\/a><\/h3>\n\n\n\n
When using the ABS()<\/code> 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.<\/p>\n\n\n\n
For example, the int data type in SQL Server has a value range from -2,147,483,648 to 2,147,483,647. <\/p>\n\n\n\n
If you apply the the ABS()<\/code> 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<\/p>\n\n\n\n
The following snippet illustrates the arithmetic overflow error:<\/p>\n\n\n
DECLARE<\/span> @x INT<\/span>;  \nSET<\/span> @x = -2147483648<\/span>;  \nSELECT<\/span> ABS<\/span>(@x);  \nGO <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
Arithmetic overflow error converting expression to data type int.<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
4) Using the ABS function with table data #<\/a><\/h3>\n\n\n\n
First, create a table<\/a> called temperature_readings<\/code> that store the temperature reading:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> temperature_readings (\n     id<\/span> INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n     recorded_at DATETIME,\n     temperature DECIMAL<\/span>(5<\/span>, 2<\/span>)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span>\n  temperature_readings (recorded_at, temperature)\nVALUES<\/span>\n  ('2024-04-04 08:00:00'<\/span>, 24.5<\/span>),\n  ('2024-04-04 08:15:00'<\/span>, 25.2<\/span>),\n  ('2024-04-04 08:30:00'<\/span>, 26.8<\/span>),\n  ('2024-04-04 08:45:00'<\/span>, 23.9<\/span>),\n  ('2024-04-04 09:00:00'<\/span>, 25.7<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, calculate absolute temperature differences from the reference temperature, which is 25 degrees Celsius:<\/p>\n\n\n
SELECT<\/span>\n  recorded_at,\n  temperature,\n  ABS<\/span>(temperature - 25<\/span>) AS<\/span> temperature_diff_from_ref\nFROM<\/span>\n  temperature_readings;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
recorded_at         | temperature | temperature_diff_from_ref\n--------------------+-------------+--------------------------<\/span>\n2024-04-04 08:00:00 | 24.50       | 0.50\n2024-04-04 08:15:00 | 25.20       | 0.20\n2024-04-04 08:30:00 | 26.80       | 1.80\n2024-04-04 08:45:00 | 23.90       | 1.10\n2024-04-04 09:00:00 | 25.70       | 0.70\n(5 rows)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use the ABS()<\/code> function to return the absolute value of a number.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Second, insert rows<\/a> into the temperature_readings<\/code> table:<\/p>\n\n\n