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()

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

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

## Syntax of MySQL SIGN()

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

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() 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() 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,**

### 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,

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:

**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,**

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,**

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.

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,**

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,**

## 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

- MySQL Official Documentation on
`ABS()`

. - MySQL Official Documentation on
`SIGN()`

.