Oracle DECODE Function Explained with Examples

The Oracle DECODE function can be confusing to those who have never used it. It’s a useful function for comparing values. Learn more about this function and some examples in this video.

 

Purpose of the Oracle DECODE Function

The purpose of the Oracle DECODE function is to perform an IF-THEN-ELSE function. It’s similar to a CASE statement, but CASE is a statement where DECODE is a function.

It allows you to provide a value, and then evaluate other values against it and show different results. It works similar to an IF statement within other languages.

 

Syntax

The syntax of the DECODE function is:

DECODE ( expression, search, result [, search, result]... [,default] )

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

 

DECODE Function Parameters

The parameters of the Oracle DECODE function are:

• expression (mandatory): This is the value to compare.
• search (mandatory): This is the value to compare against the expression.
• result (mandatory): This is what is returned if the search value mentioned previously matches the expression value. There can be multiple combinations of search and result values, and the result value is attached to the previous search value.
• default (optional): If none of the search values match, then this value is returned. If the default value is not provided, the DECODE function will return NULL if no matches are found.

If you compare this to an IF-THEN-ELSE statement, it would look like this:

IF (expression = search) THEN result
[ELSE IF (expression = search) THEN result]
ELSE default
END IF

 

These arguments can be of any numeric type (NUMBER, BINARY_FLOAT, BINARY DOUBLE) or character types.

If both expression and search are character types, then a character comparison is used and the returned value is a VARCHAR2 data type.

If the provided values are numeric, then Oracle determines the datatype to be returned by checking all of the other data types.

The Oracle DECODE function also uses a feature called “short-circuit evaluation“, which means that the search values are evaluated only before comparing them to the expression value, rather than evaluating all search values before comparing any of them to the expression. This means that Oracle never evaluates a search if a previous search is equal to an expression.

 

Oracle DECODE vs CASE

On another page, I go into detail on the Oracle CASE statement. It also allows for IF-THEN-ELSE functionality, similar to the DECODE function.

So, what’s the difference between the Oracle DECODE function and CASE statement?

There are a few differences:

• DECODE is an older function. CASE was introduced with version 8, and DECODE was around before then. CASE was introduced as a replacement for DECODE.
• CASE offers more flexibility than DECODE. Tasks that are hard using DECODE are easy using CASE. This means it is likely to be easier for future developers to work with.
• CASE is easier to read. Even with the formatting of SQL queries, a DECODE statement can be harder to read.
• The way they handle NULL values is different. DECODE treats NULL equal to NULL. CASE treats NULL as not equal to NULL.

When it comes to the performance of both of these functions, there is minimal difference. Some examples show that unless you’re doing iterations over millions of records, you won’t get much of a difference, and even then it will be small and depend on the CPU used. So, the performance should not be a determining factor when deciding whether to use an Oracle CASE statement or DECODE function.

 

Oracle DECODE Function with NULL Values

As I mentioned above, the Oracle DECODE function is an exception to other functions in how it handles NULL values. It treats a NULL expression and NULL search as equal (so NULL == NULL).

So, this example will return 1:

SELECT DECODE(NULL, NULL, 1, 0) FROM DUAL;

The CASE statement treats NULL values as not equal, so this is an important distinction when working with this function.

 

Can You Use The Oracle DECODE Function In The WHERE Clause?

Yes, you can use DECODE in the WHERE clause. It’s done in the same way as any other checks.

See the examples section below for an example on how to use the DECODE function in the WHERE clause.

 

Can You Use The Oracle DECODE Function With LIKE?

The DECODE function is used to find exact matches. This is one of the drawbacks when comparing it to the CASE statement, as the CASE statement can perform more advanced checks.

However, it is possible to use the Oracle DECODE function with LIKE.

You can do this with a combination of the SIGN function and the INSTR function.

• INSTR will check if a string is contained within another string
• SIGN will check if the INSTR is positive or negative

You can then check the SIGN and INSTR against a value of 1 to see if the match is found. See the examples section for more information on how to do this.

 

Can You Use The Oracle DECODE Function With Greater Than?

Yes, you can use the DECODE function with greater than, but it’s not neat. Just like using it with a LIKE comparison, it’s better to do with a CASE statement, but still possible with a DECODE.

This can be done using subtraction and a SIGN function.

For example, to check if a value is greater than 1000:

1. Use 1000 – value to get a result, which will be positive if the value is less than 1000, and negative if the value is greater than 1000.
2. Add this into a SIGN function, which returns -1 for negative and 1 for positive.
3. Compare the result to -1, and this will give you the greater than check using DECODE.

Once again, see the example section below for more information on how to do this.

 

Can You Use The Oracle DECODE Function In an UPDATE Statement?

Yes, you can use the DECODE function in UPDATE statements. See the example section below for more information.

 

What Is The Oracle DECODE Function Performance?

As mentioned earlier in this article, the performance of CASE vs DECODE is pretty similar. It shouldn’t be used as a determining factor when deciding which method to use.

As a general rule, I would recommend using a CASE statement as it’s easier to read and has more advanced logic.

 

Examples of the DECODE Function

Here are some examples of the DECODE function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This is an example of the DECODE function with one character search.

SELECT first_name, country,
DECODE(country, 'USA', 'North America') AS Decode_Result
FROM customers;

Result:

FIRST_NAME	COUNTRY	DECODE_RESULT
John	USA	North America
Sally	USA	North America
Steve	Canada	(null)
Mark	UK	(null)
Adam	USA	North America
Josh	(null)	(null)
Peter	France	(null)

You can see that when the country = ‘USA’, the DECODE_RESULT is ‘North America’, otherwise it shows NULL.

 

Example 2

This is an example of the DECODE function with two character searches.

SELECT first_name, country,
DECODE(country, 'USA', 'North America', 'UK', 'Europe') AS Decode_Result
FROM customers;

Result:

FIRST_NAME	COUNTRY	DECODE_RESULT
John	USA	North America
Sally	USA	North America
Steve	Canada	(null)
Mark	UK	Europe
Adam	USA	North America
Josh	(null)	(null)
Peter	France	(null)

Similar to Example 1, this shows different results for USA and UK.

 

Example 3

This example shows the DECODE function with a default value.

SELECT first_name, country,
DECODE(country, 'USA', 'North America', 'UK', 'Europe', 'Other') AS Decode_Result
FROM customers;

Result:

FIRST_NAME	COUNTRY	DECODE_RESULT
John	USA	North America
Sally	USA	North America
Steve	Canada	Other
Mark	UK	Europe
Adam	USA	North America
Josh	(null)	Other
Peter	France	Other

This is the same query as above, but with a default value. You can see that it shows Other instead of NULL.

 

Example 4

This is a DECODE function with many character searches.

SELECT first_name, country,
DECODE(country, 'USA', 'North America', 'Canada', 'North America', 'UK', 'Europe', 'France', 'Europe', 'Other') AS Decode_Result
FROM customers;

Result:

FIRST_NAME	COUNTRY	DECODE_RESULT
John	USA	North America
Sally	USA	North America
Steve	Canada	North America
Mark	UK	Europe
Adam	USA	North America
Josh	(null)	Other
Peter	France	Europe

This shows many different results from the DECODE function, as well as the Other for anything that was not considered.

 

Example 5

This query performs a DECODE on number values.

SELECT first_name, employees,
DECODE(employees, 1, 'Small') AS Decode_Result
FROM customers;

Result:

FIRST_NAME	EMPLOYEES	DECODE_RESULT
John	4	(null)
Sally	10	(null)
Steve	15	(null)
Mark	23	(null)
Adam	55	(null)
Josh	1	Small
Peter	(null)	(null)

As there is only one check being done, there is only one matching result in this table.

 

Example 6

This uses the Oracle DECODE function with three number searches.

SELECT first_name, employees,
DECODE(employees, 1, 'Small', 10, 'Medium', 50, 'Large', 'Unknown') AS Decode_Result
FROM customers;

Result:

FIRST_NAME	EMPLOYEES	DECODE_RESULT
John	4	Unknown
Sally	10	Medium
Steve	15	Unknown
Mark	23	Unknown
Adam	55	Unknown
Josh	1	Small
Peter	(null)	Unknown

Notice that only exact matches are found, not a range or a greater than.

 

Example 7

This example uses the DECODE function with NULL to see how it works.

SELECT first_name, country,
DECODE(country, 'USA', 'North America', NULL, 'No Country') AS Decode_Result
FROM customers;

Result:

FIRST_NAME	EMPLOYEES	DECODE_RESULT
John	USA	North America
Sally	USA	North America
Steve	Canada	(null)
Mark	UK	(null)
Adam	USA	North America
Josh	(null)	No Country
Peter	France	(null)

You can see that the record with NULL for a country is shown as No Country, and anything that does not match is shown as NULL.

 

Example 8

This is an example of using Oracle DECODE in a WHERE clause.

SELECT first_name, country
FROM customers
WHERE DECODE(country, 'USA', 'North America', 'Canada', 'North America', 'UK', 'Europe', 'France', 'Europe', 'Other') = 'North America';

Result:

FIRST_NAME	COUNTRY
John	USA
Sally	USA
Steve	Canada
Adam	USA

This only shows records where the DECODE function returns ‘North America’.

 

Example 9

This example uses DECODE to find if a value is contained in another value, similar to the LIKE function.

SELECT first_name, country,
DECODE(SIGN(INSTR(country, 'U')), 1, 'Found U', 0, 'Did not find U', 'Unsure') AS Decode_Result
FROM customers;

Result:

FIRST_NAME	COUNTRY	DECODE_RESULT
John	USA	Found U
Sally	USA	Found U
Steve	Canada	Did not find U
Mark	UK	Found U
Adam	USA	Found U
Josh	(null)	Unsure
Peter	France	Did not find U

This checks for the value of U and displays a message if it finds it or not.

 

Example 10

This is an example of DECODE with greater than functionality.

SELECT first_name, employees,
DECODE(SIGN(20 - employees), 1, 'Less than 20', -1, 'Greater than 20', 'Unsure') AS Decode_Result
FROM customers;

Result:

FIRST_NAME	EMPLOYEES	DECODE_RESULT
John	4	Less than 20
Sally	10	Less than 20
Steve	15	Less than 20
Mark	23	Greater than 20
Adam	55	Greater than 20
Josh	1	Less than 20
Peter	(null)	Unsure

As you can see, any record where employees is greater than 20 will show one value, and less than will show another value.

 

Example 11

This is an example of using an UPDATE statement with DECODE.

SELECT first_name, last_name, country
FROM customers;

Result:

FIRST_NAME	LAST_NAME	COUNTRY
John	Smith	USA
Sally	Jones	USA
Steve	Brown	Canada
Mark	Allan	UK
Adam	Cooper	USA
Josh	Thompson	(null)
Peter	Manson	France

 
UPDATE customers
SET last_name = DECODE(country, 'USA', 'American', last_name);

7 rows updated.

SELECT first_name, last_name, country
FROM customers;

Result:

FIRST_NAME	LAST_NAME	COUNTRY
John	American	USA
Sally	American	USA
Steve	Brown	Canada
Mark	Allan	UK
Adam	American	USA
Josh	Thompson	(null)
Peter	Manson	France

As you can see, those records where country is USA have had their last name updated to be ‘American’.

 

Similar Functions

Some functions which are similar to the Oracle DECODE function are:

• CASE – A statement that was introduced to replace DECODE. Offers more functionality and easier readability compared to DECODE.

You can find a full list of Oracle functions here.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: