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.

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


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,

  '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.


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.


This page was last updated on June 19, 2023.

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