Oracle – DECODE Function

decode

admin Oracle , , , , , , , ,

Hi Geeks,
DECODE is very intersting function provided by Oracle. It is SQL version of IF…THEN…ELSE… block.
Let’s have a look at the systax which will help us to understand the working scenarios of this functions.

Syntax:

DECODE(expr, search, result, default)

expr
It can be a column name from a table or a string. If it is a char string, it must be enclosed in single quoration marks.

search
User provides an argument as a string or expression which needs to be searched in expr.

result
If expr and search are equal then Oracle returns corresponding results. It can be a column name or a string.

default
If expr is not equal to search then Oracle returns default. This is an optional argument. If it is omitted, then Oracle returns null.

The maximum number of arguments in the DECODE function, including expr, searches, results, and default, are 255.

You can also use CASE Expressions to achieve the same output.
Let’s understand the concept with an example.

Example:

When we run above SQL statement, Oracle will generate below output.

LOCATION_ID COUNTRY_ID COUNTRY_NAME
1000 IT ITALY
1100 IT ITALY
1200 JP JAPAN
1300 JP JAPAN
1400 US UNITED STATES
1500 US UNITED STATES
1600 US UNITED STATES
1700 US UNITED STATES
1800 CA NO COUNTRY
1900 CA NO COUNTRY

We can see in the above table that Country_Name column is showing Countries as per the Country_IDs which we evaluated in our SQL code.
Looking at the syntax and the SQL code might confuse you.

While using DECODE, you must remember a few points.

  • First argument is always treated as expr.
  • After that search and result arguments are inter-dependent. It means these arguments are found in bound relation. e.g.
    • 2nd and 3rd
    • 4th and 5th
    • 6th and 7th
    • and so on…
  • If any argument is found alone (without it’s pair), Oracle considers it as defaul.
  • First 3 arguments are compulsory to complete a case. After that every argument which has odd index number consider as default argument if it does not have it’s pair argument.
  • In our case, ‘NO COUNTRY’ is available has index number 8 and it does not have a pair argument so for all other records which are not in our test case, Oracle has set ‘NO COUNTRY’ in COUNTRY_NAME column.

If you have any questions/suggestions, please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.
You can also learn some great SQL tricks at SQL Tricks and Workarounds.

Thank you,
Kapil Kumar

Sharing is caring!

You May Also Like..

merge

SQL – MERGE Statement

Hi Devs, As we have already learned INSERT INTO and UPDATE statement, our next very important statement is MERGE. Merge […]

update2

SQL – UPDATE Statement part-2

Hi Geeks, In previous blog on UPDATE statement, we learnt, how can we update a column value with a string […]

update

SQL – UPDATE Statement part-1

Hi Geeks, We already have a separate blog on INSERT INTO statement. So today, we will talk about UPDATE statement […]

Leave a Reply

Your email address will not be published. Required fields are marked *