Oracle PL/SQL Tutorial/Numerical Math Functions/SIGN

SIGN(0)

   <source lang="sql">

SQL> select SIGN(0) from dual;

  SIGN(0)

---

        0</source>
   
  

SIGN(-5)

   <source lang="sql">

SQL> select SIGN(-5) from dual;

 SIGN(-5)

---

       -1</source>
   
  

SIGN(5) (2)

   <source lang="sql">

SQL> select SIGN(5) from dual;

  SIGN(5)

---

        1</source>
   
  

SIGN(x) gets the sign of x. SIGN() returns -1 if x is negative, 1 if x is positive, or 0 if x is zero.

The following example displays the sign of -5, 5, and 0, respectively:

   <source lang="sql">

SQL> SQL> SELECT SIGN(-5), SIGN(5), SIGN(0) FROM dual;

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

---

---------- ----------

       -1          1          0

SQL></source>

Use SIGN function to deal with column data

   <source lang="sql">

SQL> SQL> SQL> SQL> -- create demo table SQL> create table myTable(

 2    id           NUMBER(2),
 3    value        NUMBER(6,2)
 4  )
 5  /

Table created. SQL> SQL> -- prepare data SQL> insert into myTable(ID, value)values (1,9)

 2  /

1 row created. SQL> insert into myTable(ID, value)values (2,2.11)

 2  /

1 row created. SQL> insert into myTable(ID, value)values (3,3.44)

 2  /

1 row created. SQL> insert into myTable(ID, value)values (4,-4.21)

 2  /

1 row created. SQL> insert into myTable(ID, value)values (5,10)

 2  /

1 row created. SQL> insert into myTable(ID, value)values (6,3)

 2  /

1 row created. SQL> insert into myTable(ID, value)values (7,-5.88)

 2  /

1 row created. SQL> insert into myTable(ID, value)values (8,123.45)

 2  /

1 row created. SQL> insert into myTable(ID, value)values (9,98.23)

 2  /

1 row created. SQL> SQL> select * from myTable

 2  /
       ID      VALUE

---

----------

        1          9
        2       2.11
        3       3.44
        4      -4.21
        5         10
        6          3
        7      -5.88
        8     123.45
        9      98.23

9 rows selected. SQL> SQL> SELECT id, value, SIGN(value) FROM myTable

 2  /
       ID      VALUE SIGN(VALUE)

---

---------- -----------

        1          9           1
        2       2.11           1
        3       3.44           1
        4      -4.21          -1
        5         10           1
        6          3           1
        7      -5.88          -1
        8     123.45           1
        9      98.23           1

9 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table myTable

 2  /

Table dropped. SQL> SQL></source>