SQL - ABS() Function

---

---

The SQL ABS() function accepts a single numeric value as an argument and returns the corresponding absolute value for this numeric value.

The absolute value is defined as the distance of a particular point on a number line from zero, irrespective of its direction. Since, the absolute value does not take the direction into consideration, it is never negative. Therefore, for this method, if the argument passed is not negative, the original argument is returned. But if the argument passed is negative, the result will be the negation of this argument.

Syntax

Following is the syntax of SQL ABS() function −

ABS(number)

where, number is the value for which we need to find the absolute value.

Example

If we pass a positive value as an argument to the SQL ABS() function, it returns the same value as shown below −

SELECT ABS(0.8)
AS Aboslute_Value

When we run above program, it produces following result −

+----------------+
| Aboslute_Value |
+----------------+
| 0.8            |
+----------------+

Example

If we pass a negative value as an argument to this function, it returns the same value without the negative sign as shown below −

SELECT ABS(-55787)
AS Aboslute_Value

While executing the above code we get the following output −

+----------------+
| Aboslute_Value |
+----------------+
| 55787          |
+----------------+

Example

If we pass the mathematical constant PI as an argument, this function returns its equivalent constant value as shown below −

SELECT ABS(PI())
AS Aboslute_Value

Following is an output of the above code −

+------------------+
| Aboslute_Value   |
+------------------+
| 3.14159265358979 |
+------------------+

Example

If the value passed is NULL or, in case of an error, this function returns NULL.

SELECT ABS(NULL)
AS Aboslute_Value

Output of the above code is as follows −

+----------------+
| Aboslute_Value |
+----------------+
| NULL           |
+----------------+

Example

If we pass the value to this function in the form of a string, it returns the same value without negative sign(if given).

SELECT ABS('-225')
AS Aboslute_Value

Following is the output of the above code −

+----------------+
| Aboslute_Value |
+----------------+
| 225            |
+----------------+

Example

If the value passed to this function is neither null nor a numeric value, it raises an error.

SELECT ABS('string')
AS Aboslute_Value

We get the following output while executing the above code −

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

Example

Assume we have created a table with name CUSTOMERS as shown below −

create table CUSTOMERS(ID INT NOT NULL, 
NAME VARCHAR(20) NOT NULL, 
AGE INT NOT NULL, 
ADDRESS CHAR(25), 
SALARY DECIMAL(18, 2), 
PRIMARY KEY(ID));
Commands completed successfully.

Let us insert r values into it −

insert INTO CUSTOMERS VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
insert INTO CUSTOMERS VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
insert INTO CUSTOMERS VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
insert INTO CUSTOMERS VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
insert INTO CUSTOMERS VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
insert INTO CUSTOMERS VALUES(6, 'Komal', 22, 'MP', 4500.00);
insert INTO CUSTOMERS VALUES(7, 'Muffy', 24, 'Indore', 10000.00);
()
()
()
()
()
()
()

Following query calculates the EPF (equal to 12% of the basic salary) of all the customers that is deducted from the salary per month −

SELECT SALARY, 
abs(SALARY*0.12) 
as EPF 
FROM customers;

The result produced is as follows −

+----------+-----------+
| SALARY   | EPF       |
+----------+-----------+
|  2000.00 |  240.0000 |
|  1500.00 |  180.0000 |
|  2000.00 |  240.0000 |
|  6500.00 |  780.0000 |
|  8500.00 | 1020.0000 |
|  4500.00 |  540.0000 |
| 10000.00 | 1200.0000 |
+----------+-----------+