MySQL - ABS() Function

MySQL provides a set of functions to perform various mathematical operations on numerical data.

The MySQL ABS() function is used to find the absolute value of a number. An absolute value is generally 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.

This function accepts an integer value as a parameter and returns the absolute value for the given integer. This absolute value returned is displayed as a result-set.

Syntax

Following is the syntax for MySQL ABS() function −

ABS(x)

Parameters

This function takes a numeric value as a parameter.

Return Value

This function returns the absolute (non-negative) value of the given numeric value.

Example

Following is an example of the MySQL ABS() function −

SELECT ABS(225) As Result;

Output

This will produce the following result −

Result
225

Example

If you pass a negative integer to this function the resultant value will be the same integer without the negative sign.

SELECT ABS(-55787) As Result;

Output

The above query will produce the following result −

Result
55787

Example

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

SELECT ABS(NULL) As Result;

Output

This will produce the following result −

Result
NULL

Example

You can also pass a value to this function in the form of a string as follows −

SELECT ABS('-225') As Result;

Output

The above query will produce the following result −

Result
225

Example

If the value passed is not null and not an integer value, this function returns 0 −

SELECT ABS('test') As Result;

Output

This will produce the following result −

Result
0

Example

In the following query, we are creating a MySQL table named CUSTOMERS using the CREATE statement as follows −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The below query inserts 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

Execute the following SELECT statement to retrieve all the records present in the CUSTOMERS table −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Here, we are using the MySQL ABS() function on the CUSTOMERS table to find the absolute value of the SALARY column −

SELECT NAME, ABS(SALARY) AS ABSOLUTE_SALARY
FROM CUSTOMERS;

Output

Following are the absolute values of SALARY column in CUSTOMERS table −

NAME ABSOLUTE_SALARY
Ramesh 2000.00
Khilan 1500.00
Kaushik 2000.00
Chaitali 6500.00
Hardik 8500.00
Komal 4500.00
Muffy 10000.00
Advertisements