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 maybe 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 an approximately numeric data types. Remember that, it will not accept the Bit data type.

  • If the Numeric_Expression argument is positive or negative Zero, SIGN function will return Zero as result.
  • If the Numeric_Expression argument is Negative Number, SIGN function will return Negative One(-1) as result.
  • If the Numeric_Expression argument is positive Number, SIGN function will return Positive One(+1) as 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

Below lines of code is used to declare float variable and assigning the random value.

DECLARE @i INT
SET @i = 10

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

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

In the below statement, We used SIGN 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]

SQL 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 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