In SQLite, ABS() is a built-in scalar function that returns the absolute value of its argument.

The absolute value is the non-negative equivalent of the argument. It can be thought of as the distance from zero that the number resides on the number line, without considering direction.

For example, if the argument is -8 the absolute value is 8. If the argument is 8, the absolute value is also 8.

Syntax

The syntax goes like this:

abs(X)

Where X is the number in question.

• If X is a string or blob that cannot be converted to a numeric value, abs() returns 0.0.
• If X is NULL, then abs() returns NULL.
• If X is the integer -9223372036854775808 then abs() throws an integer overflow error. This is because there is no equivalent positive 64-bit two complement value.

Example

Here’s an example of how abs() works:

SELECT abs(-75);

Result:

75

We get the same result if the number is positive:

SELECT abs(75);

Result:

75

Non-Numeric Argument

If the argument is a string or blob that cannot be converted to a numeric value, abs() returns 0.0.

Example:

SELECT abs('Negative Ten');

Result:

0.0

NULL Argument

If the argument is NULL, then abs() returns NULL.

First, let’s set SQLite to show NULL values:

.nullvalue null

Now let’s run abs() with a NULL value:

SELECT abs( NULL );

Result:

null

The reason I started with .nullvalue null is because, by default, the SQLite command line returns an empty string for NULL values. By specifying .nullvalue null, we were able to see the word null whenever we encounter NULL values.

Error on Large Numbers

If the argument is the integer -9223372036854775808 then abs() throws an integer overflow error. This is because there is no equivalent positive 64-bit two complement value:

SELECT abs( -9223372036854775808 );

Result:

Runtime error: integer overflow

Passing the positive equivalent doesn’t return an error, but it returns the result in scientific notation:

SELECT abs( 9223372036854775808 );

Result:

9.22337203685478e+18

If we reduce the value by 1, then we’re fine with both numbers:

SELECT 
    abs( -9223372036854775807 ) AS Negative,
    abs( 9223372036854775807 ) AS Postive;

Result:

Negative             Postive            
-------------------  -------------------
9223372036854775807  9223372036854775807

Missing Argument

Calling abs() without an argument results in an error:

SELECT abs();

Result:

Parse error: wrong number of arguments to function abs()
  SELECT abs();
         ^--- error here