Oracle SIGN Function: A Simple Guide

The SIGN function in Oracle SQL is a simple mathematical function that tells you the sign of a number. It doesn't return the number itself, but rather an indicator of whether the number is positive, negative, or zero.

This function is very useful for data validation or in CASE statements to apply different logic based on a value's sign.

What is the SIGN Function in Oracle?

The SIGN(n) function checks the number n and returns one of three possible values:

• 1 if the number n is positive (greater than 0).
• -1 if the number n is negative (less than 0).
• 0 if the number n is exactly 0.

For floating-point numbers (BINARY_FLOAT, BINARY_DOUBLE), it returns +1 for NaN (Not a Number) as well.

SIGN Function Syntax

The syntax for SIGN is very simple:

SIGN(n)

Let's break that down:

• n (the number): The number whose sign you want to check. This can be a number, a column, or the result of a calculation.

Oracle SIGN Function Examples

Here are two practical examples of how to use SIGN.

Example 1: Checking a Positive, Negative, and Zero Value with SIGN

This basic example shows the output for all three possible results.

Query:

SELECT 
  SIGN(45) AS "Positive_Sign",
  SIGN(-15) AS "Negative_Sign",
  SIGN(0) AS "Zero_Sign"
FROM DUAL;

Result:

Positive_Sign Negative_Sign  Zero_Sign
------------- ------------- ----------
            1            -1          0

Example 2: Using SIGN in a WHERE Clause

This example shows how to find all products in a warehouse that have a positive stock quantity (meaning they are in stock).

Query: (This assumes you have a table named inventory with a quantity_on_hand column.)

SELECT 
  product_name,
  quantity_on_hand
FROM inventory
WHERE SIGN(quantity_on_hand) = 1;

Result: (The query would return all products where quantity_on_hand is greater than 0.)

PRODUCT_NAME      QUANTITY_ON_HAND
----------------- ----------------
Laptop            50
Wireless Mouse    120
Keyboard          75