Open In App

PLSQL | SIGN Function

Improve
Improve
Like Article
Like
Save
Share
Report

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.


Last Updated : 25 Oct, 2019
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads