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 article.
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:
1DECODE ( 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:
1IF (expression = search) THEN result
2[ELSE IF (expression = search) THEN result]
3ELSE default
4END 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:
1SELECT 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:
- 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.
- Add this into a SIGN function, which returns -1 for negative and 1 for positive.
- 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.
1SELECT first_name, country,
2DECODE(country, 'USA', 'North America') AS Decode_Result
3FROM 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.
1SELECT first_name, country,
2DECODE(country, 'USA', 'North America', 'UK', 'Europe') AS Decode_Result
3FROM 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.
1SELECT first_name, country,
2DECODE(country, 'USA', 'North America', 'UK', 'Europe', 'Other') AS Decode_Result
3FROM 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.
1SELECT first_name, country,
2DECODE(country, 'USA', 'North America', 'Canada', 'North America', 'UK', 'Europe', 'France', 'Europe', 'Other') AS Decode_Result
3FROM 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.
1SELECT first_name, employees,
2DECODE(employees, 1, 'Small') AS Decode_Result
3FROM customers;
Result:
| FIRST_NAME | COUNTRY | 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.
1SELECT first_name, employees,
2DECODE(employees, 1, 'Small', 10, 'Medium', 50, 'Large', 'Unknown') AS Decode_Result
3FROM customers;
Result:
| FIRST_NAME | COUNTRY | 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.
1SELECT first_name, country,
2DECODE(country, 'USA', 'North America', NULL, 'No Country') AS Decode_Result
3FROM 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) | 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.
1SELECT first_name, country
2FROM customers
3WHERE 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.
1SELECT first_name, country,
2DECODE(SIGN(INSTR(country, 'U')), 1, 'Found U', 0, 'Did not find U', 'Unsure') AS Decode_Result
3FROM 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.
1SELECT first_name, employees,
2DECODE(SIGN(20 - employees), 1, 'Less than 20', -1, 'Greater than 20', 'Unsure') AS Decode_Result
3FROM customers;
Result:
| FIRST_NAME | COUNTRY | 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.
1SELECT first_name, last_name, country
2FROM customers;
Result:
| FIRST_NAME | COUNTRY | DECODE_RESULT |
|---|---|---|
| John | Smith | USA |
| Sally | Jones | USA |
| Steve | Brown | Canada |
| Mark | Allan | UK |
| Adam | Cooper | USA |
| Josh | Thompson | (null) |
| Peter | Manson | France |
1
2UPDATE customers
3SET last_name = DECODE(country, 'USA', 'American', last_name);
17 rows updated.
1SELECT first_name, last_name, country
2FROM customers;
Result:
| FIRST_NAME | COUNTRY | DECODE_RESULT |
|---|---|---|
| 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: