MySQL Tutorial/Math Numeric Functions/SIGN

Sign a list of data

   <source lang="sql">

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></source>

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

   <source lang="sql">

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></source>