SIGN

The SIGN function returns the sign of Expression.

SQL syntax

SIGN (Expression)

Parameters

SIGN has the parameter:

Parameter Description

Expression

Operand or column can be any numeric data type.

Description

  • If Expression is of type NUMBER, the data type returned is NUMBER with maximum precision and scale. Otherwise, the data type returned is TT_INTEGER.

    For numeric types that are not binary floating-point numbers, the sign is:

    • -1 if the value of Expression is <0

    • 0 if the value of Expression is = 0

    • 1 if the value of Expression is > 0

  • For binary floating-point numbers (BINARY_FLOAT and BINARY_DOUBLE), this function returns the sign bit of the number. The sign bit is:

    • -1 if the value of Expression is <0

    • +1 if the value of Expression is >= 0 or the value of Expression is equal to NaN

Examples

These examples illustrate use of the SIGN function with different data types. Table signex has been created and the columns have been defined with different data types. First, describe the table signex to see the data types of the columns. Then select each column to retrieve values for that column. Use the SIGN function to return the sign for the column. 

Command> DESCRIBE signex;

Table SAMPLEUSER.SIGNEX:
  Columns:
    COL1                            TT_INTEGER
    COL2                            TT_BIGINT
    COL3                            BINARY_FLOAT
    COL4                            NUMBER (3,2)

1 table found.
(primary key columns are indicated with *)
Command> SELECT col1 FROM signex;
< 10 >
< -10 >
< 0 >
3 rows found.
Command> SELECT SIGN (col1) FROM signex;
< 1 >
< -1 >
< 0 >
3 rows found.
Command> SELECT col2 FROM signex;
< 0 >
< -3 >
< 0 >
3 rows found.
Command> SELECT SIGN (col2) FROM signex;
< 0 >
< -1 >
< 0 >
3 rows found.
Command> SELECT col3 FROM signex;
< 3.500000 >
< -3.560000 >
< NAN >
3 rows found.
Command> SELECT SIGN (col3) FROM signex;
< 1 >
< -1 >
< 1 >
3 rows found.
Command> SELECT col4 FROM signex;
< 2.2 >
< -2.2 >
< 0 >
3 rows found.
Command> SELECT SIGN (col4) FROM signex;
< 1 >
< -1 >
< 0 >
3 rows found.