Cloud

sign

The sign() function returns a sign of an argument. The returned values are -1 if the argument is less than zero, 1 if the argument is greater than zero, 0 if the argument is equal to zero.

Syntax

The syntax for the sign() function is:

SIGN(x)

The sign() function requires one argument:

  • x: An expression that evaluates to a number.

Examples

Sign of a number

This example demonstrates how the sign() function can be used to obtain the sign of a number:

SELECT
    SIGN(0.1) AS "SIGN(0.1)",
    SIGN(999) AS "SIGN(999)",
    SIGN(0) AS "SIGN(0)",
    SIGN(-0) AS "SIGN(-0)";

The query will return the signs of the passed arguments:

 SIGN(0.1) | SIGN(999) | SIGN(0) | SIGN(-0)
-----------+-----------+---------+----------
         1 |         1 |       0 |        0

Note: -0 is accepted as an argument and is equal to zero

sign() function with an expression

This example demonstrates how to use the sign() function with an expression:

SELECT SIGN(100 - 200);

will return the sign of the expression evaluation:

 sign
------
   -1
------

Use the sign() function with a table

This example demonstrates how to use the sign() function with a table to obtain the absolute values of all numbers in a specific column:

  1. Create a table signTable containing an value column with some positive, negative and equal to zero values:

    CREATE TABLE signTable(value float);

INSERT INTO signTable(value)
VALUES
(1000),
(-200),
(0),
(0.22),
(-12.3),
(-0.0);

  2. Use this query to find the sign of all inserted values:

    SELECT value, SIGN(value) AS sign
FROM signTable;

  3. Result:

     value | sign
-------+------
  1000 |    1
  -200 |   -1
     0 |    0
  0.22 |    1
 -12.3 |   -1
    -0 |    0