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_Name  Sales  Txn_Date 
 Los Angeles 1500  Jan-05-1999 
 San Diego 250  Jan-07-1999 
 San Francisco 300  Jan-08-1999 
 Boston 700  Jan-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:

Area Sales Txn_Date
LA 1500 Jan-05-1999
SD 250 Jan-07-1999
SF 300 Jan-08-1999
Others 700 Jan-08-1999

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

Next: SQL COMMIT

This page was last updated on June 19, 2023.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact