In Oracle, the ABS() function returns the absolute value of its argument.

The absolute value is the non-negative value of a real number without regard to its sign. It describes the distance from zero that a number is on the number line, without considering direction.

Syntax

The ABS() function’s syntax goes like this:

ABS(n)

Where n can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.

The function returns the same data type as the numeric data type of the argument.

Example

Here’s an example to demonstrate:

SELECT ABS(-7)
FROM DUAL;

Result:

   ABS(-7) 
__________ 
         7 

The absolute value of -7 is 7.

That is exactly the same result we would get if we’d passed a positive 7:

SELECT 
    ABS(7),
    ABS(-7)
FROM DUAL;

Result:

   ABS(7)    ABS(-7) 
_________ __________ 
        7          7 

Null Values

Passing null to ABS() returns null:

SET NULL 'null';

SELECT ABS(null)
FROM DUAL;

Result:

   ABS(NULL) 
____________ 
        null 

By default, SQLcl and SQL*Plus return a blank space whenever a null value occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Incorrect Argument Count

Calling ABS() without passing any arguments returns an error:

SELECT ABS()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT ABS()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action:

And passing the wrong number of arguments results in an error:

SELECT ABS(1, 2)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT ABS(1, 2)
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: