SQL DECODE

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_n", "result_n"], {"default_result"} )

"search_value" is the value to search for, and "result" is the value that is displayed.

In our Table Store_Information example,

Table Store_Information

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
San Francisco	$300	Jan-08-1999
Boston	$700	Jan-08-1999

if we want to display 'LA' for 'Los Angeles', 'SF' for 'San Francisco', 'SD' for 'San Diego', and 'Others' for all other cities, we would issue the following SQL,

SELECT DECODE (store_name,
  'Los Angeles', 'LA',
  'San Francisco', 'SF',
  'San Diego', 'SD',
  'Others') Area, Sales, Date
FROM Store_Information

"Area" is the name given to the column with the DECODE statement.

Result:

To achieve what DECODE does in MySQL and SQL Server, we would use the CASE function.