How to use LIKE operator in DECODE function
April 12th, 2006 By Claudiu Ariton
How to use LIKE operator in DECODE function (for Oracle 8i version where CASE is not working):
Select
decode(NAME, (select name from dual where name like 'A%'), 'A',
(select name from dual where name like 'M%'), 'M',null)
From EMP;
Best regards
Claudiu Ariton
June 1st, 2006 at 1:33 am
select
ename,
decode(instr(ename,’M'),1,’M',
decode(instr(ename,’A'),1,’A'))
from emp;
June 1st, 2006 at 1:35 am
or with case
select ename,
case
when ename like ‘A%’ then ‘A’
when ename like ‘M%’ then ‘M’
end
from emp;
June 1st, 2006 at 2:28 am
Hello,
You are rigth but ….
The CASE operator is not working in Oracle 8i version and the INSTR function is limited than DECODE. Try to re-write the query like this with INSTR function:
Select
decode(NAME, (select name from dual where name like ‘A%R_D_E%’), ‘ARDE’,
(select name from dual where name like ‘M%’), ‘M’,null)
From EMP;
Claudiu Ariton
June 1st, 2006 at 11:05 am
do you really need it? translating “like” in multiple instr is possible, but I advise you to upgrade to a supported release of Oracle (for example 10gR2)
June 18th, 2006 at 12:46 pm
When I have to write huge DECODE a translate it into CASE.
It’s much more flexible.
It was in 8i too, try it ( Some people said it wasn’t CUBE, ROLLUP in 8i It was myth)
For more then 3 condition statement i use CASE, for simple 2 conds DECODE is quicker to write.
..
select
case
when name like ‘A%’ then ‘String begin with A’
when name like ‘M%’ then ‘String begin with M’
end as ‘result’
from emp
/
cheers
June 19th, 2006 at 10:00 am
[code]
select decode(substr(ename,1,1),'A','stringA','M','stringM') from emp;
[/code]
in this case
June 23rd, 2006 at 1:26 am
Hello,
In 8i version:
select (case when 1=1 then 1 else 0 end) test from dual; – works in SQL, but not works in PLSQL
1 declare
2 cursor c is select (case when 1=1 then 1 else 0 end) test
3 from dual;
4 begin
5 null;
6* end;
SQL> /
cursor c is select (case when 1=1 then 1 else 0 end) test
*
EROARE la linia 2:
ORA-06550: line 2, column 21:
PLS-00103: Encountered the symbol “CASE” when expecting one of the following:
( – + mod not null others
Thank you,
Claudiu