DECODE
Decode will act as value by value substitution.
For every value of field, it will checks for a match in a series of if/then tests.
What is DECODE function in SQL?
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 or If a match is not found, then default is returned. If default is omitted, then Oracle returns null.
Syntax: decode (value, if1, then1, if2, then2, ……. else);
or decode(column_name,1,’xxx’,2,’yyy’)
Ex:
select sal, decode(sal,500,'Low',5000,'High','Medium') from emp;output will be
| SAL | DECODE(SAL,500,’LOW’,5000,’HIGH’,’MEDIUM’) |
|---|---|
| 800 | Medium |
| 1600 | Medium |
| 1250 | Medium |
| 2975 | Medium |
| 1250 | Medium |
| 2850 | Medium |
| 2450 | Medium |
| 3000 | Medium |
| 5000 | High |
| 1500 | Medium |
| 1100 | Medium |
| 950 | Medium |
| 950 | Medium |
| 3000 | Medium |
| 1300 | Medium |
Here salary is 500 then decode will ‘low’ . salary is 5000 then decode will be ‘High’ ,rest are Medium . There is no 500 salary is present in employee table. So no data will retrieve .
output on tool
Other Example
| input |
|---|
| COL-1 |
| M |
| F |
| M |
| F |
| F |
| M |
Change To
| output |
|---|
| COL-1 |
| F |
| M |
| F |
| M |
| M |
| F |
We have data see on table. Have to change data M to F, means Change data incase of ‘M’ to ‘F’ and ‘F’ to ‘M’.
select decode(col-1,'M','F','F','M') from table-1;NOTE:-
- 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:- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i