The Oracle DECODE function can be confusing to those who have never used it. It’s a useful function for comparing values. Learn more about this function and some examples in this video.
Purpose of the Oracle DECODE Function
The purpose of the Oracle DECODE function is to perform an IF-THEN-ELSE function. It’s similar to a CASE statement, but CASE is a statement where DECODE is a function.
It allows you to provide a value, and then evaluate other values against it and show different results. It works similar to an IF statement within other languages.
Syntax
The syntax of the DECODE function is:
DECODE ( expression, search, result [, search, result]... [,default] )
While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:
DECODE Function Parameters
The parameters of the Oracle DECODE function are:
- expression (mandatory): This is the value to compare.
- search (mandatory): This is the value to compare against the expression.
- result (mandatory): This is what is returned if the search value mentioned previously matches the expression value. There can be multiple combinations of search and result values, and the result value is attached to the previous search value.
- default (optional): If none of the search values match, then this value is returned. If the default value is not provided, the DECODE function will return NULL if no matches are found.
If you compare this to an IF-THEN-ELSE statement, it would look like this:
IF (expression = search) THEN result
[ELSE IF (expression = search) THEN result]
ELSE default
END IF
These arguments can be of any numeric type (NUMBER, BINARY_FLOAT, BINARY DOUBLE) or character types.
If both expression and search are character types, then a character comparison is used and the returned value is a VARCHAR2 data type.
If the provided values are numeric, then Oracle determines the datatype to be returned by checking all of the other data types.
The Oracle DECODE function also uses a feature called “short-circuit evaluation“, which means that the search values are evaluated only before comparing them to the expression value, rather than evaluating all search values before comparing any of them to the expression. This means that Oracle never evaluates a search if a previous search is equal to an expression.
Oracle DECODE vs CASE
On another page, I go into detail on the Oracle CASE statement. It also allows for IF-THEN-ELSE functionality, similar to the DECODE function.
So, what’s the difference between the Oracle DECODE function and CASE statement?
There are a few differences:
- DECODE is an older function. CASE was introduced with version 8, and DECODE was around before then. CASE was introduced as a replacement for DECODE.
- CASE offers more flexibility than DECODE. Tasks that are hard using DECODE are easy using CASE. This means it is likely to be easier for future developers to work with.
- CASE is easier to read. Even with the formatting of SQL queries, a DECODE statement can be harder to read.
- The way they handle NULL values is different. DECODE treats NULL equal to NULL. CASE treats NULL as not equal to NULL.
When it comes to the performance of both of these functions, there is minimal difference. Some examples show that unless you’re doing iterations over millions of records, you won’t get much of a difference, and even then it will be small and depend on the CPU used. So, the performance should not be a determining factor when deciding whether to use an Oracle CASE statement or DECODE function.
Oracle DECODE Function with NULL Values
As I mentioned above, the Oracle DECODE function is an exception to other functions in how it handles NULL values. It treats a NULL expression and NULL search as equal (so NULL == NULL).
So, this example will return 1:
SELECT DECODE(NULL, NULL, 1, 0) FROM DUAL;
The CASE statement treats NULL values as not equal, so this is an important distinction when working with this function.
Can You Use The Oracle DECODE Function In The WHERE Clause?
Yes, you can use DECODE in the WHERE clause. It’s done in the same way as any other checks.
See the examples section below for an example on how to use the DECODE function in the WHERE clause.
Can You Use The Oracle DECODE Function With LIKE?
The DECODE function is used to find exact matches. This is one of the drawbacks when comparing it to the CASE statement, as the CASE statement can perform more advanced checks.
However, it is possible to use the Oracle DECODE function with LIKE.
You can do this with a combination of the SIGN function and the INSTR function.
- INSTR will check if a string is contained within another string
- SIGN will check if the INSTR is positive or negative
You can then check the SIGN and INSTR against a value of 1 to see if the match is found. See the examples section for more information on how to do this.
Can You Use The Oracle DECODE Function With Greater Than?
Yes, you can use the DECODE function with greater than, but it’s not neat. Just like using it with a LIKE comparison, it’s better to do with a CASE statement, but still possible with a DECODE.
This can be done using subtraction and a SIGN function.
For example, to check if a value is greater than 1000:
- Use 1000 – value to get a result, which will be positive if the value is less than 1000, and negative if the value is greater than 1000.
- Add this into a SIGN function, which returns -1 for negative and 1 for positive.
- Compare the result to -1, and this will give you the greater than check using DECODE.
Once again, see the example section below for more information on how to do this.
Can You Use The Oracle DECODE Function In an UPDATE Statement?
Yes, you can use the DECODE function in UPDATE statements. See the example section below for more information.
What Is The Oracle DECODE Function Performance?
As mentioned earlier in this article, the performance of CASE vs DECODE is pretty similar. It shouldn’t be used as a determining factor when deciding which method to use.
As a general rule, I would recommend using a CASE statement as it’s easier to read and has more advanced logic.
Examples of the DECODE Function
Here are some examples of the DECODE function. I find that examples are the best way for me to learn about code, even with the explanation above.
Example 1
This is an example of the DECODE function with one character search.
SELECT first_name, country,
DECODE(country, 'USA', 'North America') AS Decode_Result
FROM customers;
Result:
FIRST_NAME | COUNTRY | DECODE_RESULT |
John | USA | North America |
Sally | USA | North America |
Steve | Canada | (null) |
Mark | UK | (null) |
Adam | USA | North America |
Josh | (null) | (null) |
Peter | France | (null) |
You can see that when the country = ‘USA’, the DECODE_RESULT is ‘North America’, otherwise it shows NULL.
Example 2
This is an example of the DECODE function with two character searches.
SELECT first_name, country,
DECODE(country, 'USA', 'North America', 'UK', 'Europe') AS Decode_Result
FROM customers;
Result:
FIRST_NAME | COUNTRY | DECODE_RESULT |
John | USA | North America |
Sally | USA | North America |
Steve | Canada | (null) |
Mark | UK | Europe |
Adam | USA | North America |
Josh | (null) | (null) |
Peter | France | (null) |
Similar to Example 1, this shows different results for USA and UK.
Example 3
This example shows the DECODE function with a default value.
SELECT first_name, country,
DECODE(country, 'USA', 'North America', 'UK', 'Europe', 'Other') AS Decode_Result
FROM customers;
Result:
FIRST_NAME | COUNTRY | DECODE_RESULT |
John | USA | North America |
Sally | USA | North America |
Steve | Canada | Other |
Mark | UK | Europe |
Adam | USA | North America |
Josh | (null) | Other |
Peter | France | Other |
This is the same query as above, but with a default value. You can see that it shows Other instead of NULL.
Example 4
This is a DECODE function with many character searches.
SELECT first_name, country,
DECODE(country, 'USA', 'North America', 'Canada', 'North America', 'UK', 'Europe', 'France', 'Europe', 'Other') AS Decode_Result
FROM customers;
Result:
FIRST_NAME | COUNTRY | DECODE_RESULT |
John | USA | North America |
Sally | USA | North America |
Steve | Canada | North America |
Mark | UK | Europe |
Adam | USA | North America |
Josh | (null) | Other |
Peter | France | Europe |
This shows many different results from the DECODE function, as well as the Other for anything that was not considered.
Example 5
This query performs a DECODE on number values.
SELECT first_name, employees,
DECODE(employees, 1, 'Small') AS Decode_Result
FROM customers;
Result:
FIRST_NAME | EMPLOYEES | DECODE_RESULT |
John | 4 | (null) |
Sally | 10 | (null) |
Steve | 15 | (null) |
Mark | 23 | (null) |
Adam | 55 | (null) |
Josh | 1 | Small |
Peter | (null) | (null) |
As there is only one check being done, there is only one matching result in this table.
Example 6
This uses the Oracle DECODE function with three number searches.
SELECT first_name, employees,
DECODE(employees, 1, 'Small', 10, 'Medium', 50, 'Large', 'Unknown') AS Decode_Result
FROM customers;
Result:
FIRST_NAME | EMPLOYEES | DECODE_RESULT |
John | 4 | Unknown |
Sally | 10 | Medium |
Steve | 15 | Unknown |
Mark | 23 | Unknown |
Adam | 55 | Unknown |
Josh | 1 | Small |
Peter | (null) | Unknown |
Notice that only exact matches are found, not a range or a greater than.
Example 7
This example uses the DECODE function with NULL to see how it works.
SELECT first_name, country,
DECODE(country, 'USA', 'North America', NULL, 'No Country') AS Decode_Result
FROM customers;
Result:
FIRST_NAME | EMPLOYEES | DECODE_RESULT |
John | USA | North America |
Sally | USA | North America |
Steve | Canada | (null) |
Mark | UK | (null) |
Adam | USA | North America |
Josh | (null) | No Country |
Peter | France | (null) |
You can see that the record with NULL for a country is shown as No Country, and anything that does not match is shown as NULL.
Example 8
This is an example of using Oracle DECODE in a WHERE clause.
SELECT first_name, country
FROM customers
WHERE DECODE(country, 'USA', 'North America', 'Canada', 'North America', 'UK', 'Europe', 'France', 'Europe', 'Other') = 'North America';
Result:
FIRST_NAME | COUNTRY |
John | USA |
Sally | USA |
Steve | Canada |
Adam | USA |
This only shows records where the DECODE function returns ‘North America’.
Example 9
This example uses DECODE to find if a value is contained in another value, similar to the LIKE function.
SELECT first_name, country,
DECODE(SIGN(INSTR(country, 'U')), 1, 'Found U', 0, 'Did not find U', 'Unsure') AS Decode_Result
FROM customers;
Result:
FIRST_NAME | COUNTRY | DECODE_RESULT |
John | USA | Found U |
Sally | USA | Found U |
Steve | Canada | Did not find U |
Mark | UK | Found U |
Adam | USA | Found U |
Josh | (null) | Unsure |
Peter | France | Did not find U |
This checks for the value of U and displays a message if it finds it or not.
Example 10
This is an example of DECODE with greater than functionality.
SELECT first_name, employees,
DECODE(SIGN(20 - employees), 1, 'Less than 20', -1, 'Greater than 20', 'Unsure') AS Decode_Result
FROM customers;
Result:
FIRST_NAME | EMPLOYEES | DECODE_RESULT |
John | 4 | Less than 20 |
Sally | 10 | Less than 20 |
Steve | 15 | Less than 20 |
Mark | 23 | Greater than 20 |
Adam | 55 | Greater than 20 |
Josh | 1 | Less than 20 |
Peter | (null) | Unsure |
As you can see, any record where employees is greater than 20 will show one value, and less than will show another value.
Example 11
This is an example of using an UPDATE statement with DECODE.
SELECT first_name, last_name, country
FROM customers;
Result:
FIRST_NAME | LAST_NAME | COUNTRY |
John | Smith | USA |
Sally | Jones | USA |
Steve | Brown | Canada |
Mark | Allan | UK |
Adam | Cooper | USA |
Josh | Thompson | (null) |
Peter | Manson | France |
UPDATE customers
SET last_name = DECODE(country, 'USA', 'American', last_name);
7 rows updated.
SELECT first_name, last_name, country
FROM customers;
Result:
FIRST_NAME | LAST_NAME | COUNTRY |
John | American | USA |
Sally | American | USA |
Steve | Brown | Canada |
Mark | Allan | UK |
Adam | American | USA |
Josh | Thompson | (null) |
Peter | Manson | France |
As you can see, those records where country is USA have had their last name updated to be ‘American’.
Similar Functions
Some functions which are similar to the Oracle DECODE function are:
- CASE – A statement that was introduced to replace DECODE. Offers more functionality and easier readability compared to DECODE.
You can find a full list of Oracle functions here.
While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:
how to convert the numbers to characters like 1 to ‘positive’ and -1 to ‘negative’ using functions.
Hi Ravi, you could use the DECODE function in this article. However I recommend using the CASE function to check number and convert to ‘positive’ if 1 or ‘negative’ if -1. You can use the SIGN function to determine if a number is positive or negative, or use greater than or less than.
Sir, please send the systax.
Try this:
SELECT
CASE WHEN numvalue < 0 THEN 'negative' CASE WHEN numvalue > 0 THEN ‘positive’
ELSE ‘even’
END AS numtest
FROM yourtable;
Thank you sir.
Hi sir,
I have one table like tester2 val1 val2 val3
A B 10
C D 20
E A 25
B A 10
A E 25
D C 20
E F 25 and output is given below
val1 val2 val3
A B 10
C D 20
A E 25
E F 25
we can use the functions or joins. So,please send the syntax and which function used in this problem.
Hi Ravi, try this
SELECT DISTINCT(LEAST(VAL1,VAL2)),
GREATEST(VAL1,VAL2) ,
VAL3
FROM TESTER
ORDER BY VAL3;
Hi Sir,
I have string like x12abde#_13-89. I want to print out the only ‘xabde’ by using functions
so, give an syntax for this problem
I am having the another table in which decode values there ,how i can join the tables base on decode value
2. Write SQL query to display employees salary and the following condition using whether CASE/DECODE.
If Salary is greater than or equal to 1000 and salary is less than 2000 then add 5 to salary
If Salary is greater than or equal to 3500 and salary is less than 4000 then add 10 to salary
If Salary is greater than or equal to 4000 and salary is less than 5000 then add 20 to salary
Hi Malak, as mentioned in my other comment, what have you written already?
pls help me to solve this
Write SQL query to display employees salary and the following condition using whether CASE/DECODE.
If Salary is greater than or equal to 1000 and salary is less than 2000 then add 5 to salary
If Salary is greater than or equal to 3500 and salary is less than 4000 then add 10 to salary
If Salary is greater than or equal to 4000 and salary is less than 5000 then add 20 to salary
Hi Malak, what have you written already? Could you use the DECODE function from this article to write a query to do this?
Hi Ben,
Can you explain me how to get numbers,small and big charecters in different row from single row.
table have data like
test
1
2
A
B
a
b
c
3
4
i want output like this
num small_chr big_chr
1 a A
2 b B
3 c
4
Hi, I don’t think you can do that in SQL. Each row is a set of data or a record, and having separate columns for different combinations of rows isn’t something SQL can do. Why would you need to do this?
Hi, in Oracle we use decode with partition by to flatfile data onto one line of output.
for instance,
select id_number,
decode(max(ct,1,degree)degree1,
decode(max(ct,1,school)school1,
decode(max(ct,1,grad_year) grad_year1,
decode(max(ct,2,degree)degree2,
decode(max(ct,2,school)school2
decode(max(ct,2,grad_year)grad_year2
from
(select row_number() over (partition by education.id_number order by education.graduationdate desc) ct, degree, school, grad_year
from education
group by degree, school, grad_year)
This makes a quick and elegant way to insert data into a subselect without having to create 2 or sometimes 5 or 6 CTEs (we have grads with that many degrees) but every way I’ve attempted to do this in ms sql server leaves me with one row for each degree, usually with the data in a seperate column for each ct set. is there a way to make a case statement create output that looks like this:
ID_NUMBER| DEGREE1 | SCHOOL1 | GRAD_YEAR1 | DEGREE2 | SCHOOL2 | GRAD_YEAR2
200001 BS SAS 1984 MS SAS 1987