articles

Home / DeveloperSection / Articles / SIGN function in SQL SERVER

SIGN function in SQL SERVER

AVADHESH PATEL 4506 12-Sep-2012

SIGN is an in-build function of Transact-SQL. This is a mathematical function that returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.

Syntax

SIGN ( numeric_expression )

Example
DECLARE @value int

SET @value = -1
WHILE @value < 2
   BEGIN
      SELECT SIGN(@value)AS 'RETURN VALUE'
      SELECT @value = @value + 1
   END

 Screen Shot

SIGN function in SQL SERVER

If data type is decimal

-- DATA TYPE DECIMAL

DECLARE @value DECIMAL(4,2)
SET @value = -1
WHILE @value < 2
   BEGIN
      SELECT SIGN(@value)AS 'RETURN VALUE'
      SELECT @value = @value + 1
   END
 Screen Shot

SIGN function in SQL SERVER

Here we notice that when there is positive value the function gives positive values and if the values are negative it will return you negative values. Also we notice that if the data type is INT the return value is INT and when the value passed to the function is Numeric the result also matches it. Not every data type is compatible with this function.  Here is the quick look up of the return types.

1.       bigint -> bigint

2.       int/smallint/tinyint -> int

3.       money/smallmoney -> money

4.       numeric/decimal -> numeric/decimal

5.       everybody else -> float


Updated 07-Sep-2019
Avadhesh Kumar Patel District Project Manager - Aligarh 14 months work experience in Panchayati Raj Department Sector as District Project Manager & 12 months work experience in IT Sector as Software Engineer. :-)

Leave Comment

Comments

Liked By