SQL Essentials Training & Certification
- 11k Enrolled Learners
- Self Paced
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.
Let’s start one by one.
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:
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.
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:
SELECT DECODE(1, 1, 'One') FROM dual;
DECODE(expression , search , result [, search , result]… [, default(optional)])
The value which has to be compared. It automatically gets converted to the data type of the first search value before comparing.
The value that is returned, if expression=search.
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
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((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)
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.