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.

Syntax

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.

[ ] means that the "search_value_n", "result_n" pair can occur zero, one, or more times.

Example

We use the following table for our example.

Table Store_Information

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
San Francisco300Jan-08-1999
Boston700Jan-08-1999

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
FROM Store_Information;

"Area" is the name given to the column that the DECODE function operates on.

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 © 2017   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact