SIGN() — Indicates whether a numeric value is positive, negative, or zero.
SIGN( numeric-expression )
The SIGN() function evaluates a numeric expression and returns +1 if it is positive, -1 if it is negative, and 0 or NULL if it is zero or null, respectively. For example:
SIGN(3.1415) = 1 SIGN(5 - 12) = -1 SIGN(0) = 0
The following example uses the SIGN() and DECODE() functions to select a graphical representation of the difference between two fields. In this case, up and down arrows based on the difference between the purchase price and current price of stocks.
DocumentationSELECT name, current_price,
DECODE(SIGN(Current_price - Purchase_price),
+1, CHAR(11014), -- Up arrow
-1, CHAR(11015), -- Down arrow
0, CHAR(8212)) -- M-dash
FROM Stocks
ORDER BY name ASC;