PLSQL | SIGN Function
The PLSQL SIGN function is used for returning a value which indicates the sign of a number. The SIGN function accepts one parameter which is the number whose sign needs to be known. The SIGN function returns a value of the numeric data type. This function takes as an argument any numeric data type as well as any non-numeric data type that can be implicitly converted to a numeric data type.
The possible values returned by the SIGN function are:
- The SIGN function returns -1 if the number is smaller than 0.
- The SIGN function returns 0 if the number is equal to 0.
- The SIGN function returns 1 if the number is greater than 0.
Syntax:
SIGN (number)
Parameters Used:
number – It is used to specify the number whose sign needs to be tested.
Return Value:
The SIGN function in PLSQL returns a numeric value.
Supported Versions of Oracle/PLSQL:
- Oracle 12c
- Oracle 11g
- Oracle 10g
- Oracle 9i
- Oracle 8i
Example-1: Using positive numeric value as an argument in the SIGN function.
DECLARE Test_Number1 number := 5; BEGIN dbms_output.put_line(SIGN(Test_Number1)); END;
Output:
1
Example-2: Using negative numeric value as an argument in the SIGN function.
DECLARE Test_Number1 number := -5; BEGIN dbms_output.put_line(SIGN(Test_Number1)); END;
Output:
-1
Example-3: Using zero as an argument in the SIGN function.
DECLARE Test_Number1 number := 0; BEGIN dbms_output.put_line(SIGN(Test_Number1)); END;
Output:
0
Example-4: Using a positive number with decimal value as an argument in the SIGN function.
DECLARE Test_Number1 number := 0.0032; BEGIN dbms_output.put_line(SIGN(Test_Number1)); END;
Output:
1
Example-5: Using a negative number with decimal value as an argument in the SIGN function.
DECLARE Test_Number1 number := - 0.0032; BEGIN dbms_output.put_line(SIGN(Test_Number1)); END;
Example-6: Using SIGN function with select query.
SELECT SIGN(-5) "Sign" FROM DUAL;
Output:
Sign -1
Advantages:
The SIGN function accepts any numeric datatype as well as any non-numeric datatype as an argument that can be implicitly converted to a numeric datatype.
Contact Us