Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT SIGN(-12) AS negative, SIGN(0) AS zero, SIGN(7) AS positive;

Try it

SQL Sign of numbers, and the portable CASE version

Press Run (or Ctrl + Enter) to see the result here.

SQL Price versus a target ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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)
SQL
-- 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, 0 or 1 for a less than, equal to or greater than b. It is handy as a sort key, but beware of overflow in very large integers.
  • Direction and size: SIGN(x) * ABS(x) equals x. Splitting them lets you group by direction (SIGN(change)) and sum ABS(change).
  • Counting gains and losses: SUM(CASE WHEN SIGN(change) = 1 THEN 1 ELSE 0 END), or just SUM(change > 0) where booleans are integers (SQLite, MySQL).
  • Zero is its own case. SIGN(0) is 0, so SIGN(x) = 1 means strictly positive.
  • NULL stays NULL, and a CASE without ELSE also returns NULL for it, which is the same behavior.
  • Floating point: a tiny leftover such as 0.1 + 0.2 - 0.3 is not zero, so SIGN reports 1. Round first.