MySQL Tutorial/Math Numeric Functions/SIGN

Sign a list of data

mysql>
mysql> create table myTable(
    ->   id           int(2),
    ->   value        FLOAT(6,2)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> insert into myTable(ID,  value)values (1,9);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (2,2.11);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (3,3.44);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (4,-4.21);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (5,10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (6,3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (7,-5.88);
Query OK, 1 row affected (0.02 sec)
mysql> insert into myTable(ID,  value)values (8,123.45);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (9,98.23);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (10,0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (11,984.23);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (12,0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (13,928.87);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (14,0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (15,918.3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (16,9.23);
Query OK, 1 row affected (0.00 sec)
mysql> insert into myTable(ID,  value)values (17,8.23);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from myTable;
+------+--------+
| id   | value  |
+------+--------+
|    1 |   9.00 |
|    2 |   2.11 |
|    3 |   3.44 |
|    4 |  -4.21 |
|    5 |  10.00 |
|    6 |   3.00 |
|    7 |  -5.88 |
|    8 | 123.45 |
|    9 |  98.23 |
|   10 |   0.00 |
|   11 | 984.23 |
|   12 |   0.00 |
|   13 | 928.87 |
|   14 |   0.00 |
|   15 | 918.30 |
|   16 |   9.23 |
|   17 |   8.23 |
+------+--------+
17 rows in set (0.00 sec)
mysql>
mysql>
mysql> select value, sign(value) from myTable;
+--------+-------------+
| value  | sign(value) |
+--------+-------------+
|   9.00 |           1 |
|   2.11 |           1 |
|   3.44 |           1 |
|  -4.21 |          -1 |
|  10.00 |           1 |
|   3.00 |           1 |
|  -5.88 |          -1 |
| 123.45 |           1 |
|  98.23 |           1 |
|   0.00 |           0 |
| 984.23 |           1 |
|   0.00 |           0 |
| 928.87 |           1 |
|   0.00 |           0 |
| 918.30 |           1 |
|   9.23 |           1 |
|   8.23 |           1 |
+--------+-------------+
17 rows in set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>

SIGN(X) returns the sign of the argument as or 1, depending on whether X is negative, zero, or positive

mysql>
mysql> SELECT SIGN(-32);
+-----------+
| SIGN(-32) |
+-----------+
|        -1 |
+-----------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT SIGN(0);
+---------+
| SIGN(0) |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT SIGN(234);
+-----------+
| SIGN(234) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)
mysql>