4. Understanding the Elements of SQL Statements : Expressions in SQL : CASE Expressions : DECODE Function
 
Share this page                  
DECODE Function
DECODE performs the same function as the simple case expression.
DECODE
DECODE(source_value, match_value1, decode_value1, match_value2, decode_value2,..., match_valuen, decode_valuen [, else_value])
Operand type and Result type: All data types, but the types of source_value,match_value1, match_value2, … must be union compatible, and the types of decode_value1, decode_value2, …, else_value must also be union compatible.
Compares source_value successively to match_value1, match_value2, …, match_valuen. The result is the decode_valuei of the first matching match_valuei. If none of the match_valuei have the same value as source_value, the result of the function is else_value.
Examples:
1. Decode the value state_code. If state_code is 'ak', then return 'Alaska'; if state_code is 'al', then return 'Alabama'; and so on; otherwise return "unknown".
DECODE(state_code, 'ak', 'Alaska',
                   'al', 'Alabama',
                            . . . ,
                   'wy', 'Wyoming',
                   'unknown') AS state_name;
2. Decode the value sex:
SELECT name, DECODE(sex, 'm', 'male', 'f', 'female', 'unknown') FROM person;