SIGN() Function in Oracle

by

In Oracle, the SIGN() function returns the sign of its argument as -1, 0, or 1, depending on whether the argument is negative, zero, or positive, and also depending on whether the argument is of type NUMBER or a floating-point number.

Syntax

The syntax goes like this: 

SIGN(n)

Where n can be any numeric data type, or any nonnumeric data type that can be implicitly converted to NUMBER, and returns NUMBER.

How to Decipher the Result

The actual result will depend on whether the argument is a NUMBER type, or a binary floating-point number (BINARY_FLOAT and BINARY_DOUBLE).

For value of NUMBER type, the sign is:

  • -1 if n<0
  • 0 if n=0
  • 1 if n>0

For binary floating-point numbers, the SIGN() function returns the sign bit of the number. The sign bit is:

  • -1 if n<0
  • +1 if n>=0 or n=NaN

Example

In this example, I pass a NUMBER type:

SELECT SIGN(78.50)
FROM DUAL;

Result:

   SIGN(78.50) 
______________ 
             1

Here’s another example to demonstrate various outputs with the NUMBER type:

SELECT 
    SIGN(7),
    SIGN(0),
    SIGN(-7)
FROM DUAL;

Result:

   SIGN(7)    SIGN(0)    SIGN(-7) 
__________ __________ ___________ 
         1          0          -1

Binary Floating-Point Numbers

Here’s an example of what happens when we cast those numbers as binary floating-point numbers:

SELECT 
    SIGN(CAST(7 AS BINARY_FLOAT)) AS "7",
    SIGN(CAST(0 AS BINARY_FLOAT)) AS "0",
    SIGN(CAST(-7 AS BINARY_FLOAT)) AS "-7",
    SIGN(binary_float_nan) AS "NaN"
FROM DUAL;

Result:

   7    0    -7    NaN 
____ ____ _____ ______ 
   1    1    -1      1

I also added NaN to the list (the binary_float_nan floating-point literal represents a value of type BINARY_FLOAT for which the condition IS NAN is true).

Non-Numeric Arguments

Here’s what happens when we pass a non-numeric argument that can’t be converted to a numeric data type:

SELECT SIGN('Bruce')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT SIGN('Bruce')
FROM DUAL
Error report -
ORA-01722: invalid number

Null Values

Passing null returns null:

SET NULL 'null';

SELECT SIGN(null)
FROM DUAL;

Result:

   SIGN(NULL) 
_____________ 
         null

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Incorrect Argument Count

Calling SIGN() without passing any arguments returns an error:

SELECT SIGN()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT SIGN()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action:

And passing the wrong number of arguments results in an error:

SELECT SIGN(2, 3)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT SIGN(2, 3)
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: