Oracle SQL - Function SIGN function

Introduction

SIGN function returns 1 if the argument is positive; -1 if the argument is negative; and 0 if the argument is negative.

Suppose we had a table that looked like this:

Demo

SQL>
SQL> drop TABLE my_table;

Table dropped.-- w ww  .j  a v  a2  s . c om
SQL>
SQL> CREATE TABLE my_table (
  2      LINENO NUMBER(2) not null,
  3      VALUE NUMBER(6,2) not null
  4  );

Table created.
SQL>
SQL> insert into my_table values(1,1.2);
SQL> insert into my_table values(2,123.34);
SQL> insert into my_table values(3,-12.2);
SQL> insert into my_table values(4,100);
SQL> insert into my_table values(5,48);
SQL> insert into my_table values(6,-90);
SQL> insert into my_table values(7,3.88);
SQL>
SQL> SELECT *
  2  FROM my_table
  3  ORDER BY lineno ;

    LINENO      VALUE
---------- ----------
         1        1.2
         2     123.34
         3      -12.2
         4        100
         5         48
         6        -90
         7       3.88

7 rows selected.
SQL>
SQL> SELECT lineno, value, ABS(value), SIGN(value), MOD(lineno,3)
  2  FROM my_table
  3  ORDER BY lineno ;

    LINENO      VALUE ABS(VALUE) SIGN(VALUE) MOD(LINENO,3)
---------- ---------- ---------- ----------- -------------
         1        1.2        1.2           1             1
         2     123.34     123.34           1             2
         3      -12.2       12.2          -1             0
         4        100        100           1             1
         5         48         48           1             2
         6        -90         90          -1             0
         7       3.88       3.88           1             1

7 rows selected.

SQL>

SIGN tells us whether the value is positive, negative, or zero.

MOD gives us the remainder of LINENO/3.

• Previous
• Next