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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×