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 orn
=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: