How To Use SIGN Function In Oracle

This help is based on examples so it would be easier to understand.Oracle Sign function allows to determine a number sign and the Sign function returns ONLY three possible values “-1“, “0” and “1“. The function can be used only with numbers or non-numeric data types who’s value can be converted implicitly to a number. This does mean use Oracle Sign only with values that can be treated as a number. The syntax of Oracle Sign is:

SIGN (<numeric_value>);

The following three concepts are showing what does return function Oracle Sign:

• 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 Sign function will return number 1.

 SELECT SIGN (9)
   FROM dual;

The next Oracle Sign example will use number “-8″ and since the value is smaller than zero or a negative number the Oracle Sign returns -1.

 SELECT SIGN (-8)
   FROM dual;

The third example has number zero and that makes the Oracle Sign to return number 0.

 SELECT SIGN (0)
   FROM dual;

The fourth example shows how to convert all values to positive numbers. There are situations when you would need to work only with positive numbers even originally they are negative. This SQL query is written only for an example and the same result can be achieved by using Oracle ABS() function.

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

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

One of the ways to use Oracle Sign is determine the number sign and do certain actions with it depending on the number type. This determination is used quite widely on reports or web pages to determine which text should be shown 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 loads of ways to use it and to keep the code smaller and cleaner by not using slow and complicated procedures.

See Also:
Oracle Select Oracle Rownum Oracle Home