Decode in SQL

The DECODE function in SQL simplifies conditional logic, comparing values and returning results based on matches. It streamlines if-then-else scenarios, improving query efficiency. Utilizing the DECODE in SQL enhances readability and reduces complexity in handling conditional expressions.

DECODE Function in SQL

The DECODE function in SQL, especially within Oracle databases, allows for incorporating conditional logic directly into SQL queries. This functionality enables the execution of operations similar to "if-then-else" statements found in procedural programming languages. Here, we'll delve deeper into the DECODE function by discussing its application with numeric and character types, accompanied by examples for each.

Numeric Types

For numeric types, such as NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, the DECODE in sql facilitates comparisons and operations based on numeric precedence. This means it can dynamically identify and compare numeric values, converting them as necessary to ensure they are of the same data type for accurate comparison and result computation.

Example:

Let's say we have a table employee_salaries with columns employee_id (NUMBER) and salary (NUMBER). We want to categorize employees into different salary ranges using DECODE:

In this example, the SIGN function is used to compare the salary against 10,000. DECODE then interprets the result (-1, 0, 1) to categorize each employee's salary as 'Below 10K', 'Exactly 10K', or 'Above 10K'.

Character Types

When it comes to character types (CHAR, VARCHAR2, NCHAR, NVARCHAR2), DECODE compares string values using a non-padded comparison method. This ensures that comparisons are made based on the actual text, ignoring trailing spaces, which can be a factor in fixed-length strings.

Example:

Imagine a customer_feedback table with columns feedback_id (NUMBER) and customer_satisfaction (VARCHAR2), where satisfaction levels are categorized as 'Good', 'Average', and 'Poor'. To translate these into a numerical satisfaction score using DECODE, we could do the following:

Here, DECODE in SQL assigns a numeric score to each satisfaction level: 3 for 'Good', 2 for 'Average', and 1 for 'Poor'. If the satisfaction level doesn't match any of the specified categories, a default score of 0 is assigned.

Syntax

Parameters

• Expression :
  it is the value that is to be compared. Before searching, it automatically gets converted into the data type to be searched.
• Search :
  It is the value that is compared against the search expression in SQL.
• Result :
  it is the value that the expression returns, i.e, when the "search = expression".
• Default :
  if the search does not match the expression then the default value is returned in some cases, if a default is not mentioned then it returns null.

Examples of DECODE Function in SQL

Example - 1 :

Implementing simple DECODE in sql example.

Output :

Example - 2 :

Implementing the DECODE in sql example on the employee table.

Output :

In this example, the DECODE function returned the ‘S’ column which is salary, and hence the output is sorted by the salary column.