SQL DECODE

Q: Is DECODE available in MySQL or SQL Server?

No. DECODE is an Oracle-specific function. In MySQL and SQL Server, you use the CASE expression to achieve equivalent if-then-else logic.

Q: How do you replace DECODE in MySQL or SQL Server?

Use a CASE expression: CASE column_name WHEN search_value_1 THEN result_1 ... ELSE default_result END — this is the ANSI-standard equivalent of 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.

Oracle's DECODE function maps column values to replacement values using search/result pairs — acting like an inline if-then-else. For MySQL or SQL Server, use the CASE expression to achieve the same result.

Syntax

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.

Example

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.

Frequently Asked Questions

What does the SQL DECODE function do?

DECODE is an Oracle function that compares a column value against a series of search values and returns the corresponding result for the first match found. If no match is found, it returns an optional default value — functioning like an inline if-then-else statement.

Is DECODE available in MySQL or SQL Server?

No. DECODE is Oracle-specific. In MySQL and SQL Server, use the CASE expression to achieve equivalent conditional logic.

What is the syntax for Oracle DECODE?

SELECT DECODE (column_name, search_value_1, result_1, [search_value_n, result_n], {default_result}) — the search/result pairs can repeat as many times as needed.

How do you replace DECODE in MySQL or SQL Server?

Use a CASE expression: CASE column_name WHEN 'Los Angeles' THEN 'LA' WHEN 'San Francisco' THEN 'SF' ELSE 'Others' END — this is the ANSI-standard equivalent of DECODE.

Next: SQL COMMIT

This page was last updated on March 19, 2026.

AdBlock Detected!

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