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

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.

7 Comments

Hi Ravi, you could use the DECODE function in this article. However I recommend using the CASE function to check number and convert to ‘positive’ if 1 or ‘negative’ if -1. You can use the SIGN function to determine if a number is positive or negative, or use greater than or less than.

Hi sir,
I have one table like tester2 val1 val2 val3
A B 10
C D 20
E A 25
B A 10
A E 25
D C 20
E F 25 and output is given below
val1 val2 val3
A B 10
C D 20
A E 25
E F 25
we can use the functions or joins. So,please send the syntax and which function used in this problem.