Oracle Number Function - Oracle/PLSQL SIGN Function






This Oracle tutorial explains how to use the Oracle/PLSQL SIGN function.

The Oracle/PLSQL SIGN function returns a value indicating the sign of a number.

Syntax

The syntax for the Oracle/PLSQL SIGN function is:

SIGN( number )

number is the number to test for its sign.

Sign returns:

  • 1 If The Argument Is Positive;
  • -1 If The Argument Is Negative;
  • 0 If The Argument Is Negative.




Example


SQL> select sign(10) from dual;
--   w w w.j a  v  a 2s  .  co m
  SIGN(10)
----------
         1

SQL>
SQL> select sign(-10) from dual;

 SIGN(-10)
----------
        -1

SQL>
SQL> select sign(0) from dual;

   SIGN(0)
----------
         0

SQL>




Test table value


create table TestTable(
      ID                 VARCHAR2(4 BYTE)         NOT NULL,
      MyNumber           Number(8,2)-- from ww w  .java2 s . c  o m
    );

insert into TestTable (ID, MyNumber)values('1',12);
insert into TestTable (ID, MyNumber)values('1',-1);
insert into TestTable (ID, MyNumber)values('1',1.1);

SQL> select myNumber from testTable;

  MYNUMBER
----------
        12
        -1
       1.1

SQL> select sign(myNumber) from testTable;

SIGN(MYNUMBER)
--------------
             1
            -1
             1

SQL>