SIGN() in SQL
Returns the sign of a number: -1 for negative numbers, 0 for zero, and 1 for positive numbers.
Syntax
Return type
SIGN() Function Example
What is SIGN() in SQL?
The SIGN() function in SQL returns the sign of a given number, indicating whether the value is positive, negative, or zero. Specifically, it returns:
-1if the number is negative0if the number is zero1if the number is positive
This function is useful for mathematical operations, financial calculations, and data analysis where distinguishing between positive and negative values is required. It is supported in SQL Server, MySQL, PostgreSQL, and Oracle, making it a reliable tool for handling numeric comparisons, trend analysis, and conditional logic in queries.
Parameters:
number: The numeric value whose sign is to be determined
Example Use Cases:
-- Determine direction of movement
-- Check temperature changes
-- Analyze profit/loss
Notes:
Behavior: Returns -1 for any negative number, 0 for zero, 1 for any positive number
Performance Considerations: Simple numeric operation, highly efficient
Version Info: Core mathematical function available in all major DBMS
Deprecated/Recommended Alternatives: None
Error Handling:
Error: Returns NULL if input is NULL
Recommendation: Use COALESCE if NULL handling needed
Supported Databases:
DBMS
Function / Syntax
Example
Behavior with NULL
MySQL
SIGN(X)
SIGN(-15.5)
Returns NULL
PostgreSQL
SIGN(number)
SIGN(-15.5)
Returns NULL
SQL Server
SIGN(numeric_expression)
SIGN(-15.5)
Returns NULL
SQLite
SIGN(X)
SIGN(-15.5)
Returns NULL
BigQuery
SIGN(X)
SIGN(-15.5)
Returns NULL
Snowflake
SIGN(number)
SIGN(-15.5)
Returns NULL
Athena
SIGN(number)
SIGN(-15.5)
Returns NULL
Related Functions:
ABS()
Returns the absolute (positive) value of a number by removing the negative sign if present.
ROUND()
Rounds a number to a specified number of decimal places or to the nearest integer.
FLOOR()
Rounds a number down to the nearest integer or to a specified decimal place. Always rounds toward negative infinity.
CEIL()/CEILING()
Rounds a number up to the nearest integer or to a specified decimal place. Always rounds away from zero for positive numbers and toward zero for negative numbers.
