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:

1. Oracle 12c
2. Oracle 11g
3. Oracle 10g
4. Oracle 9i
5. 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.