DECODE

Oracle

DECODE(expression, search, result [, search, result]… [, default])

• expression: This parameter represents the value to be compared. The DECODE function will check this expression against the search parameters.
• search: It specifies the value that will be compared with the expression. If the value of the expression matches with the search value, the corresponding result value will be returned.
• result: This is the value that will be returned when the value of the expression matches with the search value.
• default: This is an optional parameter. If none of the search values match with the expression, then this default value would be returned by the DECODE function. If the default parameter is omitted and none of the search values match, the DECODE function will return NULL.

Example

SELECT LAST_NAME,
DECODE(DEPARTMENT_ID,
       20, 'IT',
       30, 'HR',
       40, 'Sales',
       'Not Available') AS DEPARTMENT_NAME
FROM EMPLOYEES;

Output

LAST_NAME	DEPARTMENT_NAME
Smith	IT
Johnson	HR
Brown	Sales
Davis	Not Available

Explanation

In the given example, the DECODE function compares each DEPARTMENT_ID in the EMPLOYEES table with the specified values (20, 30, 40). If DEPARTMENT_ID matches with any of these values, it returns the corresponding department name. If there is no match, it returns ‘Not Available’. In this way, the DECODE function essentially performs a case conversion.