MS SQL Training (32 Blogs) Become a Certified Professional
AWS Global Infrastructure


Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (30 Blogs)
  • SQL Essentials Training and Certification (4 Blogs)

DECODE in SQL – Syntax of Oracle Decode Function

Last updated on May 22,2024 235.8K Views

6 / 6 Blog from Introduction To MySQL

In Oracle, DECODE function allows us to add procedural if-then-else logic to the query. In this blog, we will try to get a complete understanding of DECODE function in SQL. We will be learning the various ways to use DECODE, its syntax and understand it with examples. Stay with us until the end of the blog.

The topics that are going to be covered are:

Let’s start one by one. 

What is DECODE function in SQL?

In Oracle, DECODE function allows us to add procedural if-then-else logic to the query. DECODE compares the expression to each search value one by one. If expression is equal to a search, then the corresponding result is returned by the Oracle Database. If a match is not found, then the default is returned. If the default is omitted, then Oracle returns null.

The types of arguments can be:


If the first search-result pair is numeric, then Oracle compares all search-result expressions and the first expert to find the argument with the highest numeric precedence, converts the remaining arguments implicitly to that datatype, and returns that particular datatype.

  • Character types

If expr and search are character data, then Oracle compares them using non-padded comparison semantics. expr, search, and the results can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The returned String is of VARCHAR2 datatype and is in the same character set as the first result parameter.

Oracle Database uses short-circuit evaluation. It evaluates search values only before comparing it to expression rather than evaluating all search values. If a previous search is equal to expression, the evaluation is terminated.

Oracle converts expr and search values to the datatype of the first search value before comparison. and convert the return value to the same data type as the first result.

Upskill for Higher Salary with SQL Certification training course


Course NameUpcoming BatchesFees
SQL Certification Training15th June,2024 (Weekend Batch)₹19,995

Example: If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the data type VARCHAR2.

Two nulls are considered to be equivalent by Oracle. If expr is null, then Oracle returns NULL which is the result of the first search.

The maximum number of components that can be contained in the DECODE function is 255. This includes expression, search, and result arguments. 

The DECODE function can be used in the following versions of Oracle or PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

A basic example:

In the following example, the Oracle DECODE() function compares the first argument with the second argument. Since they are equal, the function returns to the second argument which is the string ‘One’.

DECODE(1, 1, 'One')

The syntax for the DECODE function is:

DECODE(expression , search , result [, search , result]… [, default(optional)])

DECODE in SQL - Edurekaexpression

The value which has to be compared. It automatically gets converted to the data type of the first search value before comparing.


The value that is compared against the expression.


The value that is returned, if expression=search.


If there are no matches, the DECODE function will return default and if default is omitted, then the function will return NULL.

Learn more about SQL Server and its framework from the SQL Course.

Find out about our MS SQL Course in Top Cities

SQL Training in BangaloreSQL Course in Pune
SQL Training in ChennaiSQL Course in Mumbai
SQL Training in HyderabadSQL Course in Kolkata

Examples of DECODE function

  • The DECODE function can be used in Oracle/PLSQL as follows
SELECT bank_name,
DECODE(bank_id, 001, 'SBI',
                    002, 'ICICI',
                    003, ‘Dena',
                    'Gateway') result
FROM banks;

Equivalent IF-THEN-ELSE statement for the above DECODE() statement:

IF bank_id = 001 THEN
   result := 'SBI';

ELSIF bank_id = 002 THEN
   result := 'ICICI';

ELSIF bank_id = 003 THEN
   result := 'Dena';

   result := 'Gateway';


The DECODE function will compare each bank_id value, one by one.

  • DECODE function to compare two dates (date1 and date2), where, if date1 > date2, the DECODE function should return date2. Otherwise, the DECODE function should return date1
DECODE((date1 - date2) - ABS(date1 - date2), 0, date2, date1)

The formula below equals 0, if date1 is greater than date2:

(date1 - date2) - ABS(date1 - date2)

The date example illustrated above can also be modified as follows:

DECODE(SIGN(date1-date2), 1, date2, date1)
  • DECODE statement that will return the following:

If hours_of_work < 1 then return 0.04
If hours_of_work >= 1 and < 5 then return 0.04
If hours_of_work > 5 then return 0.06

Here, you need to create a formula that will evaluate to a single number for each one of your ranges.

SELECT emp_name,
DECODE(TRUNC (( hours_of_work + 3) / 4), 0, 0.04,
                                          1, 0.04,
                                          0.06) as perc_value
FROM employees;

This is all about DECODE function, by now you must have got a clear picture of how it works and how useful this function is. Now, try using them whenever any IF-ELSE logic is required while working on SQL. I hope the article helped you with the concepts of the DECODE statement.

If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of ”DECODE in SQL” and I will get back to you.

Upcoming Batches For SQL Certification Course
Course NameDateDetails
SQL Certification Course

Class Starts on 15th June,2024

15th June

SAT&SUN (Weekend Batch)
View Details

Join the discussion

Browse Categories

webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.

image not found!
image not found!

DECODE in SQL – Syntax of Oracle Decode Function