How SIGN() Works in MariaDB

In MariaDB, SIGN() is a built-in function that returns the sign of its argument as -1, 0, or 1, depending on whether the argument is negative, zero, or positive.

Syntax

The syntax goes like this:

SIGN(X)

Where X is the value for which to return the sign.

Example 1

Here’s an example to demonstrate:

SELECT SIGN(9);

Result:

+---------+
| SIGN(9) |
+---------+
|       1 |
+---------+

In this example, the value is positive, and so 1 is returned.

Example 2

Here are some more values to demonstrate the possible outputs:

SELECT 
    SIGN(8),
    SIGN(-8),
    SIGN(0);

Result:

+---------+----------+---------+
| SIGN(8) | SIGN(-8) | SIGN(0) |
+---------+----------+---------+
|       1 |       -1 |       0 |
+---------+----------+---------+

Non-Numeric Arguments

Here’s an example of what happens when we provide non-numeric arguments:

SELECT SIGN('Nine');

Result:

+--------------+
| SIGN('Nine') |
+--------------+
|            0 |
+--------------+
1 row in set, 1 warning (0.000 sec)

Let’s see the warning:

SHOW WARNINGS;

Result:

+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Nine' |
+---------+------+------------------------------------------+

Null Argument

SIGN() returns null if its argument is null:

SELECT SIGN(null);

Result:

+------------+
| SIGN(null) |
+------------+
|       NULL |
+------------+

Incorrect Parameter Count

Calling SIGN() with the wrong number of arguments, or without any arguments results in an error:

SELECT SIGN();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'SIGN'

And:

SELECT SIGN(10, 2);

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'SIGN'