ABS Function in SQL

The ABS is a mathematical function in the Structured Query Language which returns the absolute value of the particular number.

In simple words, this function finds the distance of a given number on the number line from zero.

This function accepts single numeric or any non-numeric data and returns the data type same as the argument type.

Syntax of ABS String Function

Syntax1: This syntax uses the ABS function with the column name of the SQL table:

In this SELECT syntax, we have to specify the name of that numeric column on which we want to use ABS function.

Syntax2: We can also use the ABS function with the particular number as like the below syntax:

Examples of ABS String function

Example 1: The following SELECT query shows the absolute value of zero:

Output:

ABS_0
0

Example 2: The following SELECT query shows absolute value of the number 5:

Output:

ABS_5
5

Example 3: The following SELECT query returns the position of the 'Manufacturing Company' string in the original string:

Output:

ABS_-0.7
.7

Example 4: This example uses the ABS function with the table in Structured Query Language.

In this example, we will create the new table through which we will run the query of ABS function on numeric columns:

The following shows the syntax to create the new table in SQL:

CREATE TABLE Name_of_New_Table
(
First_Column_of_table Data Type (character_size of First Column),  
Second_Column_of_table Data Type (character_size of the Second column ),  
Third_Column_of_table Data Type (character_size of the Third column),  
.......,  
Last_Column_of_table Data Type (character_size of the Last column)
);  

The following CREATE statement creates the Product_Details table for storing the price and quantity of products:

CREATE TABLE Product_Details
(
Product_ID INT NOT NULL,
Product_Name Varchar(50),
Product_Quantity INT,
Purchasing_Price INT,
Selling_Price INT,
Release_Date Date, 
Product_Rating INT
);

The following multiple INSERT queries insert the records of products with their selling and purchasing price into the Product_Details table:

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (104, P1, 10, 94.5, NULL, 2022-04-30, -0.8);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (202, P4, 15, 45, 75, 2022-01-28, -5);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (103, P2, 18, 25, NULL, 2022-02-18, 4);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (111, P7, 25, 5, 15, 2021-12-25, 9);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (210, P6, 15, 55.50, 70, 2021-10-15, NULL);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (212, P8, 19, 110, 250, 2022-01-28, 4);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (112, P10, 10, 550, 835, 2022-04-11, NULL);

The following SELECT statement displays the inserted records of the above Product_Details table:

Product_ID	Product_Name	Product_Quantity	Purchasing_Price	Selling_Price	Release_Date	Product_Rating
104	P1	10	94.5	NULL	2022-04-30	-0.8
202	P4	15	45	75	2022-01-28	-5
103	P2	18	25	NULL	2022-02-18	4
111	P7	25	5	15	2021-12-25	9
210	P6	15	55.50	70	2021-10-15	NULL
212	P8	19	110	250	2022-01-28	4
112	P10	10	550	835	2022-04-11	NULL

Query 1: The following SELECT query uses the ABS function with the Product_ID column of the above Product_Details table:

This SQL statement returns the absolute value of the Product_ID of each product.

Output:

Product_ID	Absolute_Product_ID
104	104
202	202
103	103
111	111
210	210
212	212
112	112

Query 2: The following SELECT query uses the ABS function with the Product_Quantity column of the above Product_Details table:

SELECT Product_Name, Product_Quantity, ABS(Product_Quantity) AS Absolute_ProductQuantity FROM Product_Details;

This SQL statement returns the absolute value of quantity of each product.

Output:

Product_Name	Product_Quantity	Absolute_ProductQuantity
P1	10	10
P4	15	15
P2	18	18
P7	25	25
P6	15	15
P8	19	19
P10	10	10

Query 3: The following SELECT query uses the ABS function with the Product_Rating column of the above Product_Details table:

SELECT Product_Rating, ABS(Product_Rating) AS Absolute_rating FROM Product_Details;

This SQL statement shows the absolute value of rating of each product.

Output:

Product_Rating	Absolute_rating
-0.8	.8
-5	5
4	4
9	9
NULL	-
4	4
NULL	-

Query 4: The following SELECT query uses the ABS function with the Purchasing_Price and Selling_Price column of the above Product_Details table:

SELECT Purchasing_Price, ABS(Purchasing_Price) AS AbsolutePurchasingPrice, Selling_Price, ABS(Selling_Price) AS AbsoluteSellingPrice FROM Product_Details;

Output: