Decode SQL | Decode Function in SQL - sql - sql tutorial - learn sql
DECODE Function:
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.
Read Also
NVL function in sql.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.