What is the use of DECODE function in SQL?

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 which are going to be covered are:

• What is DECODE function in SQL?
• The syntax for the DECODE function
• Examples of DECODE function

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 default is returned. If default is omitted, then Oracle returns null.

The type of arguments can be:

• Numeric types (NUMBER, BINARY_FLOAT, or BINARY_DOUBLE)

If the first search-result pair is numeric, then Oracle compares all search-result expressions and the first expr 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 result 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 converts the return value to the same data type as the first result.

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 the 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 the second argument which is the string ‘One’.

SELECT
DECODE(1, 1, 'One')
FROM
dual;

The syntax for the DECODE function is:

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

expression

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

search

The value that is compared against the expression.

result

The value that is returned, if expression=search.

default

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

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';

ELSE
   result := 'Gateway';

END IF;

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.