SIGNUM

Return the sign of a number as a DOUBLE: -1.0, 0.0, or 1.0 (alias for SIGN).

Category: mathReturns: DOUBLEDialect: Standard

Syntax

SIGNUM(expr)

Description

## Overview Returns the sign of the input as a DOUBLE in {-1.0, 0.0, 1.0}. SIGNUM is the floating-point counterpart to SIGN and is preferred when the result must be composed directly with other DOUBLE arithmetic without an implicit integer-to-DOUBLE cast. The two functions are otherwise identical in behavior: both return 0 for zero input, both preserve NULL, and both canonicalize negative zero. ## Behavior - Accepts any numeric type. - Returns a DOUBLE: -1.0, 0.0, or 1.0. - Returns NULL if the argument is NULL. - SIGNUM(-0.0) returns 0.0. - SIGNUM(NaN) returns NaN in engines that follow IEEE 754 semantics strictly. ## Numeric precision - SIGNUM is exact: the output is always one of three representable DOUBLE values. - No rounding is performed. ## Compatibility - SIGNUM is the Java and IEEE 754 spelling of the sign function; SIGN is the SQL standard spelling. - In engines that provide both, they are fully interchangeable except possibly for return type.

Parameters

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

Examples

-- Positive integer
SELECT SIGNUM(42);
-- Result: 1.0

-- Negative integer
SELECT SIGNUM(-17);
-- Result: -1.0

-- Zero
SELECT SIGNUM(0);
-- Result: 0.0

-- Small negative fraction
SELECT SIGNUM(-0.001);
-- Result: -1.0

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

-- Column use: feed the sign into a continuous scoring formula
SELECT user_id, SIGNUM(recent_balance_change) * LN(1 + ABS(recent_balance_change)) AS scored_change
FROM finance.ledger.accounts;

Pitfalls

SIGNUM returns DOUBLE while SIGN may return INTEGER. Use SIGNUM when you want to avoid a later promotion to DOUBLE.
SIGNUM(NaN) is NaN, not 0 or NULL. Guard with a finiteness filter before aggregating over DOUBLE columns.
SIGNUM(0.0) and SIGNUM(-0.0) both return 0.0. The negative-zero bit is not preserved.
Just like SIGN, SIGNUM on the result of a subtraction near zero can flip direction due to floating-point cancellation. Compare against an epsilon when the sign must be stable.