MySQL ABS() and SIGN() [With Easy Examples]

By /
ABS And SIGN Functions

In this tutorial, we will learn about the MySQL ABS() and the MySQL SIGN() functions. The ABS() function is used to find the absolute value of a number. The absolute value of a number is the non-negative value of the number. MySQL ABS() disregards the sign of a number and returns the result as a positive number regardless.

The MySQL SIGN() function is used to return the sign of a number. The SIGN() function returns the following:

  • If number > 0, then SIGN() returns 1.
  • If number = 0, then SIGN() returns 0.
  • If number < 0, then SIGN() returns -1.

Syntax of MySQL ABS()

ABS(expression);Code language: SQL (Structured Query Language) (sql)

Where ‘expression’ can be a number or a numeric expression.

Syntax of MySQL SIGN()

SIGN(expression);Code language: SQL (Structured Query Language) (sql)

Where ‘expression’ can be a number or a numeric expression.

Examples of MySQL ABS()

Let us take a look at a few examples of ABS(). We will use aliases with the SELECT statement to make our output readable.

MySQL ABS() On A Positive Number

Let us find out the absolute value of a positive number. The absolute value of a positive number is the same as its value. We’ll demonstrate this using the following query:

SELECT ABS(14) AS AbsoluteValue;Code language: SQL (Structured Query Language) (sql)

And the output is:

Abs Positive Number

ABS() Of A Negative Number

How about finding the absolute value of a negative number? While finding the absolute value of a negative number, ABS() disregards the sign of the negative number and returns a positive number. We can see this in the below example,

SELECT ABS(-2) AS AbsoluteValue;Code language: SQL (Structured Query Language) (sql)

And the output is:

Abs Negative Number

ABS() Of Zero

The absolute value of zero is zero. We can demonstrate this below –

SELECT ABS(0) AS AbsoluteValue;Code language: SQL (Structured Query Language) (sql)

And the output is,

Abs Zero Example

MySQL ABS() On Mathematical Expressions

As I mentioned in the syntax, we can also pass a mathematical expression as a parameter to the MySQL ABS() function. Let us also take this opportunity to compare the results of expressions with and without ABS(). Consider the below query,

SELECT -6+4; 
SELECT ABS(-6+4) AS AbsoluteValue;Code language: SQL (Structured Query Language) (sql)

And the output is,

Abs Expression

As you can see, the statement with ABS() ignores the negative sign.

Examples of MySQL SIGN()

Let us look at the following examples.

SELECT SIGN(35);Code language: SQL (Structured Query Language) (sql)

And the output we get is:

Sign Positive

Since 35 is a positive number, its sign is + and hence, the output is 1. As we discussed earlier, 1 means that the number is greater than 0 and is positive.

Now let us look at another basic example. Consider the below query,

SELECT SIGN(-35);Code language: SQL (Structured Query Language) (sql)

And the output we get is,

Sign Negative

Since -35 is a negative number, its sign is – and hence, the output is -1. As we discussed earlier, -1 means that the number is less than 0 and is negative.

Finally, what if we pass 0 as the number in the SIGN() function?

SELECT SIGN(0);Code language: SQL (Structured Query Language) (sql)

And the output we get is,

Sign Zero

As we discussed in the introduction, if 0 is passed to SIGN(), then the function returns 0 itself.

ABS() and SIGN() – Working With Tables

Let us take a look at a couple of simple examples of ABS() and SIGN() while working with tables. Consider the below Shops table.

Shops Table
Shops Table

Let us find the absolute values of the profit percent values for shops with id 6 and 7. We will use the WHERE clause along with the IN operator. The query is:

SELECT ProfitPercent, ABS(ProfitPercent) FROM Shops WHERE ID IN (6, 7);Code language: SQL (Structured Query Language) (sql)

And the output is,

Shops Abs Example

Let us now find the sign of all values in the ProfitPercent column. We will use the below query,

SELECT ProfitPercent, SIGN(ProfitPercent) FROM Shops;Code language: SQL (Structured Query Language) (sql)

And the output is,

Shops Sign Example

Conclusion

ABS() and SIGN() though not widely used in general, prove quite important while working with large databases and tables for a variety of operations. I would encourage you to check out the below references.

References