DECODE() Function in Oracle

In this Oracle SQL Tutorial, We will expolre the DECODE() function in Oracle.

What is DECODE() Function in Oracle?

Oracle DECODE is a function in Oracle which help to implement if-then-else logic in SQL query. 

Oracle DECODE is use for transform the data to one value to another value.

Oracle DECODE only use for equality check logic in Oracle SQL.

Oracle DECODE function is used in different database versions like Oracle 9i,Oracle 10g,Oracle 11g and Oracle 12c.

Syntax of DECODE() function in Oracle

DECODE(value,search_value,result,default_value)

Let's understand the syntax so we can easily use the DECODE() function in Oracle SQL.

In the DECODE() function, We pass four parameter. The first parameter is your main value or it can be your table value.Second parameter is search value which mean that  second parameter value matchs with the first parameter value if it is match then third parameter return their value otherwise fourth parameter returns their value.

If it is not clear the let's understand with the DECODE() Examples.

Examples of DECODE() Function in Oracle

Example 1:-

SELECT DECODE('SQL','SQL','SQL TUTORIAL','JAVAINHAND TUTORIAL') AS DECODE_EXAMPLE_1 FROM DUAL

In this Example, I am showing when first parameter match or equal to with the second parameter then we found third parameter as return output.

Example 2:-

SELECT DECODE('SQL','SQL2','SQL TUTORIAL','JAVAINHAND TUTORIAL') AS DECODE_EXAMPLE_2 FROM DUAL

In this Example, I am showing when first parameter not match or not equal to with the second parameter then we found fourth parameter as return output.

NOTE:-

1. We can run DECODE function with three parameter like DECODE(value,search_value,result).

SELECT DECODE('SQL','SQL','SQL TUTORIAL') AS DECODE_EXAMPLE_3 FROM DUAL

2. In DECODE() function if null value match with null value then it's return true.

SELECT DECODE('','','SQL TUTORIAL') AS DECODE_EXAMPLE_4 FROM DUAL

That's all for today, I think(DECODE() Function in Oracle) post will be helpful for you. If you like the post then share your view in the comment box.