Oracle ABS Function: A Simple Guide

The ABS function in Oracle SQL is a straightforward mathematical function. Its one and only job is to return the absolute value of a number.

In simple terms, it makes any number non-negative (positive or zero). If the number is already positive, it stays positive. If the number is negative, ABS flips it to be positive.

What is the ABS Function in Oracle?

The ABS function is used when you only care about the magnitude or distance of a number from zero, not its sign (positive or negative).

This is useful for:

• Calculating Differences: Finding the difference between two values (e.g., forecast_sales and actual_sales) without caring which one was larger.
• Magnitude: Measuring total deviation or error from a target.
• Simplifying Logic: Ensuring a value is positive before using it in other calculations, like square roots.

ABS Function Syntax

The syntax for ABS is very simple:

ABS(n)

Let's break that down:

• n: The number, numeric column, or expression whose absolute value you want to find. This can be any numeric data type.

Oracle ABS Function Examples

Here are two practical examples of how to use ABS.

Example 1: Finding the Absolute Value of a Number

This example shows the most basic use of ABS on both a positive and a negative number.

Query:

SELECT 
  ABS(-25.5) AS "Negative_Case",
  ABS(25.5)  AS "Positive_Case"
FROM DUAL;

Result:

Negative_Case Positive_Case
------------- -------------
         25.5          25.5

Notice how -25.5 becomes 25.5, and 25.5 remains 25.5.

Example 2: Using ABS to Find the Difference in a Table

Imagine you have a table of product stock levels and you want to see how far each product is from a target "safe" level of 50, regardless of whether it's overstocked or understocked.

Query:

-- We find the difference (stock - 50) and then apply ABS
-- to get the 'deviation' as a positive number.
SELECT 
  product_name,
  current_stock,
  ABS(current_stock - 50) AS "Deviation_From_Target"
FROM products;

Result: (Assuming a products table with this data)

PRODUCT_NAME      CURRENT_STOCK   Deviation_From_Target
----------------- -------------   ---------------------
Wireless Mouse               70                      20
Mechanical Keyboard          40                      10
USB-C Hub                    50                       0

The mouse (70 stock) is 20 units away from 50. The keyboard (40 stock) is 10 units away from 50. ABS lets us see this deviation as a simple, positive number.