Decode in SQL

This tutorial will help you to learn the use of SQL Decode function so as to add the if-then-else logic to your queries. We shall try to understand the Decode function, its syntax through examples.

What is the use of the Decode Function in SQL?

As we discussed earlier, a Decode function in Oracle helps us to add if-then-else logic to a query procedurally. It compares an expression with each search value one after another. Suppose, if an expression is found equal, the same is returned by the Oracle DB. If there is no match found in the search, the default value will be returned. If you omit the default returned value, then, Oracle returns null.

The type of arguments in Decode can be:

• Numeric Types and
• Character types

Let’s see this one by one.

Numeric Types:

BINARY_FLOAT or BINARY_DOUBLE and a NUMBER will fall under Numeric types. If the first search result pair is a number, Oracle starts comparing the entire search result expressions. It lets the first expression with the highest number precedence in order to identify the argument and then converts the other remaining arguments indirectly to that particular data type and returns that data type.

Character Types:

Suppose if expression and the search were the character data, in that case, Oracle compares both through a technique called non-padded comparison semantics. The Expression, Search and the obtained results could be some of the following data types namely CHAR, NCHAR, VARCHAR2, and NVARCHAR2. The string returned will be a data type,  VARCHAR2. This data type will be present in the same character set as in the very first result parameter.

Generally, Oracle DB follows a method called the Short-circuit evaluation method. Unlike evaluating all search values directly; this method evaluates all the search values before comparing it to expression. The evaluation ends in case if a previous search us equal to the expression. Before comparing, Oracle converts the expression and search values of that data type of the first searched value. Then, it converts the returned value to that same data type as the first result.

Let’s get a clear idea of this concept with an example. Suppose, your first result is returned is a null or it contains the data type CHAR, oracle converts that returned value to the VARCHAR2 data type. Oracle considers two nulls as one and the same. If in case the expression is returned null, oracle also returns null, i.e the first result search.

A Decode function in SQL will be 255, which is the maximum permissible number of components including the expression argument, search argument and the result argument. You can use this Decode function in various Oracle versions or PLSQL versions such as Oracle 9i, Oracle 10g, Oracle 11g and Oracle 12c.

Let’s have an illustration to understand the Oracle Decode() function better.

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

In this example, the Decode function compares the first and second arguments. The function then returns the string ‘1’ since they were found equal.

The following is the syntax of the Oracle Decode() function:

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

Some examples of Decode Function:

We can use the Decode function in Oracle/PLSQL in the following way:

SELECT insurance_name,
DECODE(insurancecompany_id, 001, ‘ORIENTAL’,
002, ‘ROYAL SUNDARAM’,
003,’ ICICI LOMBARD’,
‘Gateway’) result
FROM insurances;

For the same Oracle Decode Statement above, we shall write its equivalent IF-THEN-ELSE statement as follows:

IF insurancecompany_id =001 THENresult := ‘ORIENTAL’;
ELSIF insurancecompany_id =002 THEN
result:= ‘ROYAL SUNDARAM’;
ELSIF insurancecompany_id =003 THEN
result:= ‘ICICI LOMBARD’;
ELSE
result:= ‘Gateway’;
END IF;

The above Decode function will compare the insurancecompany_id values one after the other.

Let’s see another example. We write a Decode function in order to compare two values, say (value1 and value2), where value1>value2, the decode function has to return the value2, else, it should return the value 1.

DECODE((value1 - value2) - ABS(value1 - value2), 0, value2, value1)(value1 - value2) - ABS(value1 - value2)

The above formula will be equal to Zero, if value1 is greater than value2. We can also modify the above Decode statement as follows:

DECODE(SIGN((value1 - value2) , 1, value2, value1)

I hope you got a clear understanding of Oracle Decode function about its working and the way it can be used.