Decode SQL | Decode Function in SQL - sql - sql tutorial - learn sql




DECODE Function:

  • DECODE is a function in Oracle and is also used to provide if-then-else type of logic to SQL.
  • It is not available in MySQL or SQL Server.
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.

Read Also

SQL Distinct.

Example:

  • We can use the following table for our example.

Table Store_Information

Store_Name Name Txn_Date
Los Angeles 1500 Jan-05-2016
San Diego 250 Jan-07-2016
San Francisco 300 Jan-08-2016
Boston 700 Jan-08-2016
  • 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-2016
SD 250 Jan-07-2016
SF 300 Jan-08-2016
others 700 Jan-08-2016
  • To achieve what DECODE does in MySQL and SQL Server, we would use the CASE function.
area sales Txn_Date
LA 1500 Jan-05-2016
SD 250 Jan-07-2016
SF 300 Jan-08-2016
others 700 Jan-08-2016
  • To achieve what DECODE does in MySQL and SQL Server, we would use the function.

Related Searches to DECODE Function in SQL - SQL tutorial