EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

SQL DECODE()

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » SQL Tutorial » SQL DECODE()

SQL DECODE()

Introduction to SQL DECODE()

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 on the basis of outcomes received from the comparison. A decode function basically performs the task of CASE statements. However, we should keep in mind that DECODE is a built-in function in ORACLE SQL databases and hence it is supported only in ORACLE 9i and above versions of ORACLE/ PL SQL. It is not recognized and supported in other database management servers such as PostgreSQL, SQL Server, MySQL etc. So, now we can use CASE statements to perform IF-THEN-ELSE logic in these databases.

Syntax and Parameters:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The basic syntax for writing DECODE function 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 mentioned syntax are:

  • expression: expression argument is the value which 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 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.

We can use the DECODE function as a part of the SELECT statement, ORDER BY etc.

How DECODE() Function works in SQL?

The first step is comparison of expression and search_1, if the expression = search_1 is TRUE then result_1 is returned. If it’s FALSE then 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. And it finally converts back the data_type of result to the data_type of the expression.

The functionality of DECODE in ORACLE with following flowchart.

functionality of DECODE

Example:

Code:

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

Output:

SQL DECODE() 1

The simple illustration of the above mentioned decode function is as follows:

Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (5,626 ratings)
Course Price

View Course

Related Courses
JDBC Training (6 Courses, 7+ Projects)PHP Training (5 Courses, 3 Project)Windows 10 Training (4 Courses, 4+ Projects)PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

Code:

IF 1 = 1
THEN result = 'One'
ENDIF;

Examples of SQL DECODE()

Given below are the examples mentioned:

Let us first create a ‘college_details’ table which contains college id, college name, location and fees for demonstration purposes.

We can use the following SQL CREATE TABLE statement to perform the task.

Code:

CREATE TABLE college_details(
college_id integer NOT NULL,
college_name character varying(255) NOT NULL,
college_location character varying(255) NOT NULL,
fees numeric NOT NULL
);

Output:

SQL DECODE() 2

Having created the table, let us now input some random data in it to work with in the subsequent exercises. We can use the following insert statements.

Code:

INSERT INTO college_details VALUES (10001, 'Indian Institute of Technology Roorkee', 'Roorkee,India', 10000);
INSERT INTO college_details VALUES (10002, 'Indian Institute of Technology Bombay', 'Mumbai,India', 10000);
INSERT INTO college_details VALUES (10004, 'California Institute of Technology', 'California ,USA', 60520);
INSERT INTO college_details VALUES (10003, 'Massachusetts  Institute of Technology', 'Massachusetts,India', 51520);

select * from college_details;

The data in the “college_details” table after performing the above mentioned INSERT operations looks something as shown below:

Output:

input some random data in it to work with in the subsequent exercises

Example #1

Simple SQL query to illustrate use of DECODE function.

Code:

SELECT college_id,
DECODE (college_id,   10003,'Massachusetts, USA',
10004, 'California, USA',
'India')
FROM college_details;

Output:

SQL DECODE() 7

In this example, we have performed a simple SQL task for categorizing colleges based on their location.

Simple illustration of above mentioned DECODE function is as follows:

Code:

IF college_id = 10003
THEN result = 'Massachusetts'
ELSE IF college_id = 10004
THEN result = 'California'
ELSE
result = 'India'
ENDIF;

Example #2

SQL query to illustrate abbreviation of college names based on the available data using DECODE function.

Code:

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:

abbreviation of college names based on the available data

In the above example, 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

SQL query to categories college fees into affordable and expensive for an Indian student, considering everything above $ 10000 as expensive.

Code:

SELECT college_id,fees,
DECODE(fees,10000,'Affordable','Expensive')
FROM college_details
ORDER BY college_id;

Output:

fees into affordable and expensive

In the above example, we have performed the following task using the DECODE function and have then ordered the result set by college_id.

Code:

IF fees = '10000'
THEN result = 'Affordable'
ELSE
result = 'Expensive'
ENDIF;

Conclusion

DECODE function is used to perform procedural IF-THEN-ELSE logic in SQL. The function is a close relative of CASE statements. It is a built-in function in ORACLE / PL SQL database management servers.

Recommended Articles

This is a guide to SQL DECODE(). Here we discuss how DECODE() function works in SQL with query examples for understanding better. You may also have a look at the following articles to learn more –

  1. SQL Bulk Insert
  2. SQL REGEXP
  3. SQL CASE Statement
  4. Metadata in SQL

SQL Training Program (7 Courses, 8+ Projects)

7 Online Courses

8 Hands-on Projects

73+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

2 Shares
Share
Tweet
Share
Primary Sidebar
SQL Tutorial
  • Functions
    • SQL Date Function
    • SQL String Functions
    • SQL Compare String
    • SQL Window Functions
    • SQL Timestamp
    • SQL TO_DATE()
    • SQL DATEADD()
    • SQL DATEDIFF()
    • ANY in SQL
    • LIKE Query in SQL
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL MOD()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL COALESCE
    • SQL BLOB
    • SQL ROW_NUMBER
    • SQL Server Replace
    • SQL Server Permission
    • T-SQL INSERT
    • SQL Ranking Function
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • SQL Enum
    • Cheat sheet SQL
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Commands
    • SQL Commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • SQL DDL Commands
    • FETCH in SQL
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • GROUP BY clause in SQL
    • SQL GROUP BY DAY
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL Order by Count
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUPING SETS
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Keys
    • SQL Keys
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • Alternate Key in SQL
    • SQL Super Key
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • Advanced
    • SQL Formatter
    • SQL Injection Attack
    • Aggregate Functions in SQL
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL INSTR()
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Clear Table
    • SQL DESCRIBE TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • SQL Delete View
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • Types of SQL Views
    • SQL Port
    • SQL Clustered Index
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL pivot
    • SQL Import CSV
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2020 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA Login

Forgot Password?

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

Special Offer - SQL Training Program (7 Courses, 8+ Projects) Learn More