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.

My Personal Notes arrow_drop_up


If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.