Decode

DECODE is a SQL function that provides similar functionality to an IF-THEN-ELSE or Case statement.

Syntax

The syntax for the decode function is:

decode(expression, search, result [,search, result]...[,default] )

• expression is the value to compare
• search is the value that is compared against expression
• result is the value returned, if expression is equal to search
• default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return NULL (no matches found).

Examples

Decoding code values:

SELECT decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')
  FROM employees;

SELECT DECODE(day#, 1, 'Monday',
                    2, 'Tuesday',
                    3, 'Wednesday',
                    4, 'Thursday',
                    5, 'Friday',
                    6, 'Saturday',
                    7, 'Sunday',
                    'Invalid day')
  FROM tableX;

Comparing values:

SELECT a, b, decode( abs(a-b), 0, 'a = b',
                              a-b, 'a > b',
                              'a < b')
  FROM tableX;

Aggregating values:

SELECT dept, sum(  decode(sex,'M',1,0)) MALE,
             sum(  decode(sex,'F',1,0)) FEMALE,
             count( decode(sex,'M',1,'F',1) ) TOTAL
  FROM my_emp_table
 GROUP BY dept;

Decode and NULL

As a rule, comparison to NULL should always return NULL. However, DECODE is an exception as it evaluates NULL == NULL:

SQL> SELECT decode(null,null,1,0) FROM dual;
DECODE(NULL,NULL,1,0)
---------------------
                    1

Also see

• Case