Use SIGN to see if a number is positive, negative, or zero.
It's as simple as it sounds.
Apply SIGN() to an integer or float column to get:
- SIGN(0) gives 0
- SIGN(POSITIVE_NUM) gives 1
- SIGN(NEGATIVE_NUM) gives -1
Caution! Be careful of near-zero floats that are not actually zero.
For example, SIGN(0.0000001) will return 1 since it is a positive number.
This can happen when you have round-off errors when storing FLOATS in a database. For example, 3.0 might be stored as 2.9999999...
π The SIGN() function can be implemented with a CASE WHEN. See example 2 below.
