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

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:

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!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.