The SQL SIGN function is a mathematical function that is used to determine the sign of a numeric expression. The function returns a value of -1, 0, or 1, depending on whether the input expression is negative, zero, or positive, respectively.
The syntax of the SIGN function is as follows:
Here, numeric_expression is the expression whose sign needs to be determined.
The SIGN function can be used with any numeric data type such as INT, BIGINT, FLOAT, DECIMAL, etc.
Let’s consider an example to better understand the usage of the SIGN function:
SELECT SIGN(-10) AS result;
In this example, the SIGN function is applied to the numeric value -10. Since the value is negative, the function returns -1 as the result. Therefore, the output of the above SQL statement would be: -1.
Similarly, we can apply the SIGN function to positive and zero values as well:
SELECT SIGN(20) AS result;
In this case, the value 20 is positive, so the function returns 1 as the result: 1.
SELECT SIGN(0) AS result;
In this case, the value 0 is neither positive nor negative, so the function returns 0 as the result: 0.
In conclusion, the SIGN function is a useful mathematical function in SQL that can be used to determine the sign of a numeric expression. It is particularly useful in applications where we need to distinguish between positive, negative, and zero values.