Tutorial Gateway

SQL SIGN Function

by

The SQL SIGN function is one of the SQL Mathematical function, which is used to return sign of the specified expression, and the sign may be Positive (+1), Negative (-1) or Zero (0).

SQL SIGN Function Syntax

The basic syntax of the SQL Server SIGN Function is as shown below:

SELECT SIGN (Numeric_Expression)
FROM [Source]

Numeric_Expression: This function accepts exact numeric or approximately numeric data types. Remember that it will not take the Bit data type.

  • If the Numeric_Expression argument is positive or negative zero, the SIGN function will return Zero as a result.
  • If the Numeric_Expression argument is Negative Number, the SIGN function will return Negative One(-1) as a result.
  • If the Numeric_Expression argument is positive Number, the SIGN function returns Positive One(+1) as a result.

SQL SIGN Function Example 1

In this example, We are going to find the sign values of different data (positive and negative values) and display the output

DECLARE @i INT
SET @i = 10

SELECT SIGN(@i)AS [Sign Result 1]

-- Finding Degrees directly
SELECT SIGN(150) AS [Sign Result 2]
SELECT SIGN(14.25) AS [Sign Result 3]

SELECT SIGN(0) AS [Sign Result 4]
SELECT SIGN(-0) AS [Sign Result 5]

SELECT SIGN(-25) AS [Sign Result 6]
SELECT SIGN(-104.449) AS [Sign Result 7]

OUTPUT

SQL SIGN Function 1

ANALYSIS

Next, we are finding the Sign of @i. We used the SQL ALIAS Column to assign a new name to the SQL Server result as ‘Sign Result 1’.

SELECT SIGN(@i)AS [Sign Result 1]

In the below statement, We used SIGN Mathematical function directly on the positive values.

SELECT SIGN(150) AS [Sign Result 2]
SELECT SIGN(14.25) AS [Sign Result 3]

Next, We used SIGN function on positive and negative zero

SELECT SIGN(0) AS [Sign Result 4]
SELECT SIGN(-0) AS [Sign Result 5]

Next, We used SIGN function directly on the negative values

SELECT SIGN(-25) AS [Sign Result 6]
SELECT SIGN(-104.449) AS [Sign Result 7]

SIGN Function Example 2

In this example, We are going to find the sign of all the records present in the [Service Grade] column using the DEGREES Function.

SELECT [EnglishProductName]
      ,[Color]
      ,[StandardCost]
      ,[SalesAmount]
      ,[TaxAmt]
      ,[Service Grade]
      ,SIGN([Service Grade]) AS [Service_Sign]
  FROM [SQL Tutorial].[dbo].[Mathemetical Functions]

OUTPUT

SQL SIGN Function 2

Placed Under: SQL