Oracle PL/SQL/Numeric Math Functions/SIGN

select sign( 1 2 ) ) "Using expressions"

 
SQL>
SQL>
SQL> select sign( 100 - ( 50 * 2 ) ) "Using expressions"
  2  from dual
  3  /
Using expressions
-----------------
                0
1 row selected.
SQL> --

select sign( 123 * "Using expressions"

 
SQL>
SQL> select sign( 123 * -1 + 122) "Using expressions"
  2  from dual
  3  /
Using expressions
-----------------
               -1
1 row selected.
SQL>
SQL> --

SIGN(0)

SQL> select SIGN(0) from dual;
   SIGN(0)
----------
         0
SQL>
SQL>

SIGN(5)

SQL> select SIGN(5) from dual;
   SIGN(5)
----------
         1
SQL>

SIGN: Returns 1 if the argument is positive, 0 if the argument is negative

SQL>
SQL> -- create demo table
SQL> create table TestTable(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    MyName             VARCHAR2(10 BYTE),
  4    MyDate             DATE,
  5    MyNumber           Number(8,2)
  6  )
  7  /
Table created.
SQL>
SQL>
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("1","Alison",to_date("19960711","YYYYMMDD"),12.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("2","Jason",to_date("19970622","YYYYMMDD"),-12.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("3","Smith",to_date("19980513","YYYYMMDD"),22.1);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("4","Tailor",to_date("19990624","YYYYMMDD"),-2.12);
1 row created.
SQL> insert into TestTable (ID, MyName, MyDate, MyNumber) values("5","Darlene",to_date("20000415","YYYYMMDD"),2.1);
1 row created.
SQL>
SQL>
SQL> select * from TestTable;
ID   MYNAME     MYDATE      MYNUMBER
---- ---------- --------- ----------
1    Alison     11-JUL-96      12.12
2    Jason      22-JUN-97     -12.12
3    Smith      13-MAY-98       22.1
4    Tailor     24-JUN-99      -2.12
5    Darlene    15-APR-00        2.1
SQL>
SQL>
SQL>
SQL> -- SIGN: Returns 1 if the argument is positive, 0 if the argument is negative.
SQL>
SQL>
SQL> select MyNumber, SIGN(MyNumber) from TestTable;
  MYNUMBER SIGN(MYNUMBER)
---------- --------------
     12.12              1
    -12.12             -1
      22.1              1
     -2.12             -1
       2.1              1
SQL>
SQL>
SQL>
SQL>
SQL> drop table TestTable;
Table dropped.
SQL>
SQL>

-SIGN(x): Returns -1 if x is negative, 1 if x is positive, or 0 if x is zero

SQL> --SIGN(x): Returns -1 if x is negative, 1 if x is positive, or 0 if x is zero.
SQL>
SQL> select SIGN(-5) from dual;
  SIGN(-5)
----------
        -1
SQL>

Use sign function in user-defined function

 
SQL>
SQL> CREATE OR REPLACE FUNCTION get_num (
  2     p_highval             NUMBER,
  3     p_lowval              NUMBER := 0,
  4     p_negatives_allowed   BOOLEAN := FALSE,
  5     p_scale               PLS_INTEGER := 0
  6  )
  7     RETURN NUMBER
  8  IS
  9     l_ret   NUMBER;
 10     l_sign   NUMBER := 1;
 11  BEGIN
 12     IF (p_negatives_allowed) THEN
 13        l_sign := SIGN (DBMS_RANDOM.random);
 14     END IF;
 15
 16     l_ret := l_sign * ROUND (DBMS_RANDOM.VALUE (p_lowval, p_highval), p_scale);
 17     RETURN l_ret;
 18  END;
 19  /
Function created.

Use SIGN in PL/SQL statement

 
SQL>
SQL> set serveroutput on
SQL>
SQL> BEGIN
  2     DBMS_OUTPUT.PUT_LINE(SIGN(3.5));
  3     DBMS_OUTPUT.PUT_LINE(SIGN(-3.5));
  4     DBMS_OUTPUT.PUT_LINE(SIGN(0));
  5  END;
  6  /
1
-1
0
PL/SQL procedure successfully completed.
SQL>
SQL>