Oracle SIGN() Function

Oracle SIGN() is a built-in function that returns -1, 0, or 1 to indicate the given number is negative, zero or positive.

Oracle SIGN() syntax

Here is the syntax for the Oracle SIGN() function:

SIGN(num)

Parameters

num

Required.

Return Value

The Oracle SIGN() function returns -1, 0, or 1 to indicate whether the given number is negative, zero, or positive. The implementation rules of this function are as follows:

• For values ​​of NUMBER type:

  • If n is less than 0, the sign is-1
  • If n equals to 0, the sign is 0
  • If n is greater than 0, the sign is 1

• For binary floating-point numbers (BINARY_FLOAT and BINARY_DOUBLE), this function returns the sign bit of the number:

  • If n is less than 0, the sign is -1.
  • If n is greater than or equal to 0, or n equals to NaN, the sign is 1.

If any parameter is NULL, SIGN() will return NULL.

Oracle SIGN() Examples

Here are some examples that demonstrate the usage of the Oracle SIGN() function.

Basic Usage

SELECT
    SIGN(5),
    SIGN(0),
    SIGN(-5)
FROM dual;

Output:

   SIGN(5)    SIGN(0)    SIGN(-5)
__________ __________ ___________
         1          0          -1

NaN

For NaN values, the Oracle SIGN() function returns 1.

SELECT
    SIGN(BINARY_DOUBLE_NAN),
    SIGN(BINARY_FLOAT_NAN)
FROM dual;

Output:

   SIGN(BINARY_DOUBLE_NAN)    SIGN(BINARY_FLOAT_NAN)
__________________________ _________________________
                         1                         1

NULL Parameters

If any parameter is NULL, SIGN() will return NULL.

SET NULL 'NULL';
SELECT
    SIGN(NULL)
FROM dual;

Output:

   SIGN(NULL)
_____________
         NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle SIGN() is a built-in function that returns -1, 0, or 1 to indicate the given number is negative, zero or positive.