SQL Reference ยท Scalar Functions
SQL SIGN() function
Reference for the SQL SIGN() function: returns -1, 0 or 1 for the sign of a number, NULL handling, use in comparisons and a portable CASE alternative.
SIGN(x) tells you whether a number is negative, zero or positive. It returns -1 for negative numbers, 0 for zero and 1 for positive numbers. It is a compact way to compare two values (SIGN(a - b)), to flag gains and losses, and to separate direction from size when combined with ABS().
SIGN is available in PostgreSQL, MySQL, SQL Server and Oracle. It is not part of stock SQLite, although the SQLite build on this site includes it as an extension function. The portable alternative is a CASE expression.
SELECT SIGN(-12) AS negative, SIGN(0) AS zero, SIGN(7) AS positive;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
SIGN(x)
| Name | Description |
|---|---|
x |
A number. |
Returns: -1, 0 or 1. The type matches the input where the database has a numeric type for it (PostgreSQL returns numeric for numeric input and a double for double precision). NULL gives NULL.
Dialect differences
| Database | Support |
|---|---|
| SQLite | not in stock builds (use CASE); the sql.js build on this site includes sign() as an extension function; text that is not a number gives 0 there |
| PostgreSQL | SIGN(x) |
| MySQL | SIGN(x) |
| SQL Server | SIGN(x) |
| Oracle | SIGN(x) |
-- Portable everywhere CASE WHEN x > 0 THEN 1 WHEN x < 0 THEN -1 WHEN x = 0 THEN 0 END
Usage notes
- Comparison trick:
SIGN(a - b)is-1,0or1for a less than, equal to or greater thanb. It is handy as a sort key, but beware of overflow in very large integers. - Direction and size:
SIGN(x) * ABS(x)equalsx. Splitting them lets you group by direction (SIGN(change)) and sumABS(change). - Counting gains and losses:
SUM(CASE WHEN SIGN(change) = 1 THEN 1 ELSE 0 END), or justSUM(change > 0)where booleans are integers (SQLite, MySQL). - Zero is its own case.
SIGN(0)is0, soSIGN(x) = 1means strictly positive. NULLstaysNULL, and aCASEwithoutELSEalso returnsNULLfor it, which is the same behavior.- Floating point: a tiny leftover such as
0.1 + 0.2 - 0.3is not zero, soSIGNreports1. Round first.