The syntax of sql decode function is,
DECODE (expression,search,result[,default])
where search, result can be repeated many times as needed.
And default is optional.
How Decode Works
DECODE compares expression to each search value one by one. If expression is equal to a search, then corresponding result is returned. If expression does not match with search then Oracle returns default. As default is optional so it can be omitted and if omitted, then Oracle returns null.
Always remember that in a DECODE function, Oracle considers two nulls to be equivalent. So, if expression is null, then Oracle returns the result of the first search that is also null.
The maximum number of components in the DECODE function, including expression, searches, results, and default, is 255.
Data Conversion
- Oracle automatically converts expression and each search value to the datatype of the first search value before comparing. So the datatype of the first search value is a key role in decode.
- Oracle automatically converts the return value to the same datatype as the first result. So the first result plays a key role here.
- If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2. Again for the result it depends on the first datatype.
An example that demonstrate DECODE:
With an example below I will demonstrate DECODE, about how it works.
1)Create a table named country_list and insert data into it.
SQL> create table country_list(country_name varchar2(100));
Table created.
SQL> insert into country_list values('UK');
1 row created.
SQL> insert into country_list values('USA');
1 row created.
SQL> insert into country_list values('BAN');
1 row created.
SQL> insert into country_list values('PAK');
1 row created.
SQL> insert into country_list values('IND');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from country_list;
COUNTRY_NAME
----------------------------------
UK
USA
BAN
PAK
IND
2)Now use DECODE function.
If the column value is UK then it will show United Kingdom.
If value is USA then it will show United States of America.
If value is BAN then it will show Bangladesh.
If value is PAK then it will show Pakistan.
If neither one matches then it will show default value OTHERS.
SQL> col country_name for a10
SQL> select country_name,
2 decode(country_name,'UK','United Kingdom',
3 'USA','United States of America',
4 'BAN','Bangladesh',
5 'PAK','PAKISTAN',
6 'OTHERS') from country_list;
COUNTRY_NA DECODE(COUNTRY_NAME,'UK'
---------- ------------------------
UK United Kingdom
USA United States of America
BAN Bangladesh
PAK PAKISTAN
IND OTHERS
You can also implement greater than or less than inside sql decode function. In case of number you can achieve that using SIGN function and in case of character you can achieve that using GREATEST function. Below is an example of using greatest function which will display whether name started less than M character or not.
SQL> select country_name, decode(
2 greatest(substr(country_name,1,1),'M'),'M'
3 ,'Name is between A to M','Name is between N to Z') from country_list;
COUNTRY_NA DECODE(GREATEST(SUBSTR
---------- ----------------------
UK Name is between N to Z
USA Name is between N to Z
BAN Name is between A to M
PAK Name is between N to Z
IND Name is between A to M
Related Documents
Awesome! You are the man.
ReplyDelete