Online Tech Support

This Online Tech Support tutorial page is based on examples. Oracle Sign function allows determining a number sign and the function returns ONLY three possible values “-1“, “0” and “1“. The Oracle Sign can be used only with all numbers or non-numeric data types whose value can be converted implicitly to number. In short, use Oracle Sign only with values that can be treated as a number. Function Oracle Sign syntax is following:

SIGN (<numeric_value>);

The following three concepts are showing how Oracle Sign basically works:

• Oracle Sign returns -1 if a number is smaller than zero (x < 0)
• Oracle Sign returns 0 if a number is zero (x = 0)
• Oracle Sign returns 1 if a number is greater than zero (x > 0)

The following Oracle Sign example will return the sign of number 9. Take a look at the concepts above; number 9 is greater than zero so the function returns number 1.

 SELECT SIGN (9)
   FROM dual;

The second example is using number -8 and since the value is smaller than zero or in other words a negative number the Oracle Sign returns -1.

 SELECT SIGN (-8)
   FROM dual;

Now has left to try number zero that returns number 0 by the Oracle Sign.

 SELECT SIGN (0)
   FROM dual;

The next Oracle Sign example shows how to make all numbers greater than -1. There are cases where you would need to work only with positive numbers even originally they are negative. This SQL query is only an example and can be solved by using Oracle ABS function but the example shows quite well how works Oracle Sign.

 SELECT my_number,
        my_number * SIGN (my_number) AS signed_number
   FROM (SELECT - 6 AS my_number
           FROM dual ) ;

The SQL query output shows the original number -6 in column “MY_NUMBER” and how the value has changed positive in column “SIGNED_NUMBER“. Also take a look at the SQL query to see how we did it.

Another way to use Oracle Sign is determine the number sign and do certain actions depending on the sign. This determination is used quite widely on reports or web pages to determine which text to show depending on the amount. The following example shows text “You lost” for a negative number and for rest “You gain“. This way organised messages are better to read for users and less confusing.

 SELECT DECODE(SIGN(my_number),-1,'You lost ','You gain ')||
                        my_number||' USD' AS My_description
   FROM
    (SELECT DECODE ( MOD(rownum,3),0,
                           rownum * -1,rownum) AS my_number
       FROM dual
        CONNECT BY rownum < 11 ) ;

The Oracle Sign is a simple function but there are ways to use it and make the code smaller and tidier.


See Also:
Oracle Select Oracle Decode Home