The Sign of a Number

In arithmetic, a number is considered as being negative (less than 0), null (equal to 0), or positive (higher than 0). When a number is negative, it must have a - symbol to its left. If it is positive, it may display a + symbol to its left or it can omit it. A number without the - or + symbol to its left is considered positive, also referred to as unsigned. The symbol that determines whether a number is positive or negative is referred to as its sign. The sign is easily verifiable if you know the number already. In some cases, when a number is submitted to your application, before taking any action, you may need to get this piece of information.

To find out if a value is positive, null, or negative, Transact-SQL provides the SIGN() function. Its syntax is:

SIGN(Expression)

This function takes as argument a number or an expression that can be evaluated to a number. The interpreter would then examine the number:

• If the Expression is positive, the function returns 1. Here is an example:
   

  DECLARE @Number As int; SET @Number = 24.75; SELECT SIGN(@Number) AS [Sign of 1058]; GO

• If the Expression is null, the function returns 0
   

  DECLARE @Number As int; SET @Number = 0; SELECT SIGN(@Number) AS [Sign of Number]; GO

• If the Expression is negative, the function returns -1
   

  DECLARE @Number As int; SET @Number = -57.05; SELECT SIGN(@Number) AS [Sign of -57.05]; GO

Based on this, you can use the SIGN() function to find out whether a value is negative, null, or positive: simply pass the value (or a variable) to SIGN() and use a logical operator to check its sign.