Home > SQL Server, T-SQL > SQL SERVER – SIGN() – Math Functions @SQLSERVER

SQL SERVER – SIGN() – Math Functions @SQLSERVER


SIGN() function returns the sign of a numeric expression or a value.  The data type of the return value is same as the function argument data type. This function takes any type numeric value such as integer, float, etc and returns a value to represent the sign. The sign is denoted using 1 or -1 or 0. This function may raise a numeric overflow error if the given numeric value exceeds the max range of the data type.

Sign representation

  • 1     =  Positive sign
  • -1   =  Negative sign
  • 0    =  Value Zero

Data type Mapping

This table represents the data type mapping between function argument and the return value.

Argument type                 ==>        Return type

  • bigint                                                  bigint
  • int/smallint/tinyint                          int
  • money/smallmoney                         money
  • numeric/decimal                              numeric/decimal
  • Others                                                float

Examples

select sign(123)
Output: 1
 
select sign(-123)
Output: -1
 
select sign(0)
Output: 0
 
select sign(+-0)
Output:  0
 
select sign(0.00)
Output: 0.00
 
select sign(‘-232.3’)
Output: -1
 
select sign(-99999999999999999999999999999999999999)
Output: -1
 
select sign(-999999999999999999999999999999999999999)
Output:
Msg 1007, Level 15, State 1, Line 1
The number ‘999999999999999999999999999999999999999’ is out of the range for numeric representation (maximum precision 38).
 
Example for arithmetic overflow error
Declare @tint as tinyint
Set @tint = 256
select sign(@tint)
 
Output
NULL
Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 256.
 
How to use Sign() function in TSQL?
Declare @Value as int = 1
Declare @strValue as varchar(10)
Select @strValue = Case sign (@Value)
           when 1 then ‘+ve number’
           when -1 then ‘-Ve number’
           when 0 then  ‘Zero’
       end
Print @strvalue
 

Happy programming!!! Thanks for reading.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: