Home » ABS Function in SQL

ABS Function in SQL

by Online Tutorials Library

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:

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

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

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:

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:

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:

Output:

Purchasing_Price AbsolutePurchasingPrice Selling_Price AbsoluteSellingPrice
94.5 94.5 NULL
45 45 75 75
25 25 NULL
5 5 15 15
55.50 55.5 70 70
110 110 250 250
550 550 835 835

You may also like