SQL > Advanced SQL >
DECODE is a function in Oracle and is used to provide if-then-else type of logic to SQL. It is not available in MySQL or SQL Server.
The syntax for DECODE is:
SELECT DECODE ( "column_name", "search_value_1", "result_1",
"search_value" is the value to search for, and "result" is the value that is displayed.
[ ] means that the "search_value_n", "result_n" pair can occur zero, one, or more times.
We use the following table for our example.
To display 'LA' for 'Los Angeles', 'SF' for 'San Francisco', 'SD' for 'San Diego', and 'Others' for all other cities, we use the following SQL,
SELECT DECODE (Store_Name,
'Los Angeles', 'LA',
'San Francisco', 'SF',
'San Diego', 'SD',
'Others') Area, Sales, Txn_Date
"Area" is the name given to the column that the DECODE function operates on.
To achieve what DECODE does in MySQL and SQL Server, we would use the CASE function.