SQL DECODE

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,

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 October 12, 2024.

AdBlock Detected!