SQL > Advanced SQL > DECODE Function

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.

For example, assume we have the following Store_Information table,

Table Store_Information

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
San Francisco300Jan-08-1999
Boston700Jan-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, Txn_Date
FROM Store_Information;

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

Result:

AreaSalesTxn_Date
LA1500Jan-05-1999
SD250Jan-07-1999
SF300Jan-08-1999
Others700Jan-08-1999

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

Next: SQL AUTO INCREMENT




Copyright © 2014   1keydata.com   All Rights Reserved.     Privacy Policy