top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What is the use of DECODE?

0 votes
317 views
What is the use of DECODE?
posted Aug 11, 2015 by Vidhya Sagar

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote
 
Best answer

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_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

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:

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.

answer Aug 11, 2015 by Shivaranjini
...