Introduction

DECODE function in Standard Query Language (SQL) is used to add procedural IF – THEN – ELSE-like statements to a query. It compares a given expression with each search value one by one and returns a result based on outcomes received from the comparison. Decode in SQL function performs the task of CASE statements. 

What is if and Else in SQL?

The Transact-SQL statement that follows an IF keyword and its condition are executed if the condition is satisfied: the Boolean expression returns TRUE. The optional ELSE keyword introduces another Transact-SQL statement that is executed when the IF condition is not satisfied: the Boolean expression returns FALSE.

What is the DECODE Function in SQL?

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

The type of arguments can be:

• Numeric types
• Character types

Numeric types

If the first search-result pair is numeric, Decode in SQL, 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.

Read about: data science in India

Character Types

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

If expr and search are character data, Decode in SQL, 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.

Example

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. Decode in SQL, This includes the expression, search, and result from arguments. 

Click here to learn about coding

Decode in SQL

Syntax

The basic syntax for writing the DECODE in SQL is as follows:

DECODE (expression , search_1, result_1[, search_2, result_2], …,[,search_n,result_n] [, default]);

The parameters used in the above syntax are:

• expression: expression argument is the value that is to be searched and compared with.
• search_1, search_2, …. search_n: These are the values to be searched for and then compared with the expression argument.
• result_1, result_2, …, result_n: These arguments hold the result to be returned when the given comparison returns true. For example, if expression = search_1 then the result will be result_1.
• default: default argument holds the default value. It is more or less like the ELSE statement in IF-THEN-ELSE.

How does DECODE in SQL Work?

The first step is the comparison of expression and search_1, if the expression = search_1 is TRUE then result_1 is returned. If it’s FALSE then the DEFAULT value is returned. The DECODE function automatically converts or casts the expression to the data type of the first search argument or search_1. Decode in SQL, And it finally converts back the data_type of the result to the data_type of the expression.

Example:

SELECT

DECODE(1, 1, ‘One’)

FROM dual;

Output:

DECODE(1,1,”ONE”)

ONE

Also read: data analyst course in Pune

Example of Decode in SQL

Example 1. 

The DECODE in SQL 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 in SQL 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 date example illustrated above can also be modified as follows:

DECODE(SIGN(date1-date2), 1, date2, date1)

Example 2. 

SELECT college_id, DECODE(college_name,’Massachusetts  Institute of Technology,

‘MIT’,’ California Institute of Technology, ‘CalTech’, ‘IIT’) as college_name

FROM college_details

ORDER BY college_id;

Output

we have performed the following IF-THEN-ELSE logic statements and then ordered the entire result set by college_id.

Code

IF college_name = ‘Massachusetts  Institute of Technology’

THEN result = ‘MIT’

ELSE IF college_name = ‘California Institute of Technology’

THEN result = ‘Caltech’

ELSE

result = ‘IIT’

ENDIF;

Example 3.

Simple SQL query to illustrate the use of the DECODE function.

Code:

SELECT college_id,

DECODE (college_id,   10003,’ Massachusetts, USA’,

10004, ‘California, USA’,

‘India’)

FROM college_details;

Output

Conclusion

DECODE in SQL compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns to default. If the default is omitted, then Oracle returns null.

Frequently Asked Questions