Oracle DECODE() Function

Oracle DECODE() is a built-in function that decodes a given parameter based on one or more given mapping relationships and returns the decoded value.

Oracle DECODE() Syntax

Here is the syntax of the Oracle DECODE() function:

DECODE(expr, search, result [, search, result ]...  [, default ])

Parameters

expr

Required. The content to be decoded.

search

Required. A match item.

result

Required. The value corresponding to search.

default

Required. Default value. The default value is NULL.

These parameters can be any numberic types (NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or character types.

You can provide multiple search and result pairs, which can be viewed as a mapping table.

Return Value

The Oracle DECODE() function compares expr to each search one by one, and if expr is equal to search, the function returns the corresponding result. If no match is found, the function returns default (the default value is NULL).

If any parameter is NULL, DECODE() will return NULL.

Oracle DECODE() Examples

Here are some examples that demonstrate the usage of the Oracle DECODE() function.

Basic Usage

The Oracle DECODE() function is useful for translating content, such as translating status values to status descriptions.

We simulate a result set that contains order statuses using UNION:

SELECT 10001 order_id, 1 order_status FROM dual
UNION
SELECT 10002, 2 FROM dual
UNION
SELECT 10003, 3 FROM dual
UNION
SELECT 10004, 4 FROM dual;

Output:

   ORDER_ID    ORDER_STATUS
___________ _______________
      10001               1
      10002               2
      10003               3
      10004               4

The following statement translates the order statuses to status descriptions:

SELECT
    t.*,
    DECODE(
        order_status,
        1, 'Pending',
        2, 'Paid',
        3, 'Shipped',
        'Unknown'
    ) order_status_desc
FROM
    (
        SELECT 10001 order_id, 1 order_status FROM dual
        UNION
        SELECT 10002, 2 FROM dual
        UNION
        SELECT 10003, 3 FROM dual
        UNION
        SELECT 10004, 4 FROM dual
    ) t;

Output:

   ORDER_ID    ORDER_STATUS ORDER_STATUS_DESC
___________ _______________ ____________________
      10001               1 Pending
      10002               2 Paid
      10003               3 Shipped
      10004               4 Unknown

In this example, the order statuses 1, 2, and 3 are decoded into their corresponding values, and 4 is decoded into the default value Unknown. If you do not provide the default value parameter, DECODE() will decode 4 into NULL, as follows:

SET NULL 'NULL';
SELECT
    t.*,
    DECODE(
        order_status,
        1, 'Pending',
        2, 'Paid',
        3, 'Shipped'
    ) order_status_desc
FROM
    (
        SELECT 10001 order_id, 1 order_status FROM dual
        UNION
        SELECT 10002, 2 FROM dual
        UNION
        SELECT 10003, 3 FROM dual
        UNION
        SELECT 10004, 4 FROM dual
    ) t;

Output:

   ORDER_ID    ORDER_STATUS ORDER_STATUS_DESC
___________ _______________ ____________________
      10001               1 Pending
      10002               2 Paid
      10003               3 Shipped
      10004               4 NULL

NULL Parameter

If any of the parameters is NULL, DECODE() will return NULL.

SET NULL 'NULL';
SELECT
    DECODE(NULL, NULL, NULL)
FROM dual;

Output:

DECODE(NULL,NULL,NULL)
_________________________
NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle DECODE() is a built-in function that decodes the given parameters based on one or more given mapping relationships and returns the decoded value.