DECODE Expression

Syntax

DECODE(expr, search, result {, search, result} [, default])

Oracle compares expr to each search value one by one. If expr is equal to a search, Oracle returns the corresponding result. If no match is found, Oracle returns default, or, if default is omitted, returns null.

The search, result, and default values can be derived from expressions.

Example

This query decodes the value DEPTNO. If DEPTNO is 10, the query returns 'ACCOUNTING'; if DEPTNO is 20, it returns 'RESEARCH'; etc. If DEPTNO is not 10, 20, 30, or 40, the query returns 'NONE'.

SELECT DECODE (deptno,10, 'ACCOUNTING',
20, 'RESEARCH',
30, 'SALES',
40, 'OPERATION',
'NONE') name, loc
FROM dept;

Equivalents in other databases

TABLE 70. Equivalents in other databases

IBM DB2	Simple CASE expression
Microsoft SQL Server	Simple CASE expression