SIGN

Return the sign of a number as -1, 0, or 1.

Category: mathReturns: INTEGERDialect: Standard

Syntax

SIGN(expr)

Description

## Overview Returns the sign of the input as -1 for negative values, 0 for zero, and 1 for positive values. SIGN is the standard primitive for extracting directional information from a signed quantity while discarding magnitude. Combined with ABS, SIGN factorizes any number x into SIGN(x) * ABS(x). This decomposition is useful for applying nonlinear transforms (such as signed square root or signed log) while preserving the direction of the original value. ## Behavior - Accepts any numeric type. - Returns an integer-typed value: -1, 0, or 1 (or the DOUBLE equivalents -1.0, 0.0, 1.0 for DOUBLE input, depending on engine). - Returns NULL if the argument is NULL. - SIGN(-0.0) in DOUBLE arithmetic typically returns 0, not -1; negative zero is canonicalized. - SIGN(NaN) is engine-defined but typically NaN or NULL. ## Numeric precision - SIGN is exact: no rounding occurs. - For DECIMAL inputs, the output is typically an integer. - For DOUBLE inputs, the output is typically a DOUBLE but always an integer-valued one in {-1, 0, 1}. ## Compatibility - Conforms to the SQL standard scalar function SIGN. - SIGNUM is a common alias that returns a DOUBLE-typed result with the same three possible values.

Parameters

Name	Type	Description
`expr`		Specifies the numeric value whose sign is returned. Accepts any numeric type.

Examples

-- Positive integer
SELECT SIGN(42);
-- Result: 1

-- Negative integer
SELECT SIGN(-17);
-- Result: -1

-- Zero
SELECT SIGN(0);
-- Result: 0

-- Floating-point input
SELECT SIGN(-3.14);
-- Result: -1

-- NULL propagation
SELECT SIGN(NULL);
-- Result: NULL

-- Column use: mark directional changes
SELECT trade_id, SIGN(close_price - open_price) AS direction
FROM finance.trades.orders;

Pitfalls

SIGN returns an integer on some engines and a DOUBLE on others. Cast explicitly when the downstream consumer is type-sensitive.
SIGN(NaN) is engine-defined. If your DOUBLE data can contain NaN, guard with a finiteness filter before using SIGN.
SIGN is not the derivative of ABS at zero; SIGN(0) is 0, not undefined. Use a CASE expression if you need a different convention for zero.
SIGN of the result of a subtraction can be unstable near zero due to floating-point cancellation. Consider comparing against an epsilon instead: CASE WHEN ABS(a - b) < eps THEN 0 WHEN a > b THEN 1 ELSE -1 END.