FB

Oracle DECODE FunctionThe 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] )

Get Your Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

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

In 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.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Image courtesy of digitalart / FreeDigitalPhotos.net

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Get Your SQL Function Cheat Sheet Now: