anyone way to use a wildcard in a decode?

[Ryan Gaffuri]

If I can manage this, I can condense about 10 insert statements into
one and save alot of processing time.

anyway to do this with a straight decode?

ME>create table xx(y varchar2(20));

Table created.

ME>insert into xx values('ABCD');

1 row created.
ME>commit;

Commit complete.

ME>select decode(y,'%A%','XXXXX') from xx;

DECOD
-----

1 row selected.

ME>select decode(y,'%A%','XXXXX','BLAH') from xx
MASTER_TEST2@BOBAIRD>/

DECOD
-----
BLAH

[Martin Burbridge]

rgaf...@cox.net (Ryan Gaffuri) wrote in
news:1efdad5b.03061...@posting.google.com:

>
> anyway to do this with a straight decode?
>
> ME>create table xx(y varchar2(20));
>
> Table created.
>
> ME>insert into xx values('ABCD');
>
> 1 row created.
> ME>commit;
>
> Commit complete.
>
> ME>select decode(y,'%A%','XXXXX') from xx;
>
>
> DECOD
> -----
>
>
> 1 row selected.
>
> ME>select decode(y,'%A%','XXXXX','BLAH') from xx
> MASTER_TEST2@BOBAIRD>/
>
> DECOD
> -----
> BLAH

If you mean something like this you can use instr ..

SQL> var s varchar2(10)
SQL> exec :s := 'A'

PL/SQL procedure successfully completed.

SQL> select ename, decode(instr(ename,:s), 0, 'XXXXX', ename) x
2 from emp;

ENAME X
---------- ----------
SMITH XXXXX
ALLEN ALLEN
WARD WARD
JONES XXXXX
MARTIN MARTIN
BLAKE BLAKE
CLARK CLARK
SCOTT XXXXX
KING XXXXX
TURNER XXXXX
ADAMS ADAMS
JAMES JAMES
FORD XXXXX
MILLER XXXXX
ROONEY XXXXX

15 rows selected.

SQL> exec :s := 'R'

PL/SQL procedure successfully completed.

SQL> /

ENAME X
---------- ----------
SMITH XXXXX
ALLEN XXXXX
WARD WARD
JONES XXXXX
MARTIN MARTIN
BLAKE XXXXX
CLARK CLARK
SCOTT XXXXX
KING XXXXX
TURNER TURNER
ADAMS XXXXX
JAMES XXXXX
FORD FORD
MILLER MILLER
ROONEY ROONEY

15 rows selected.

Martin

[Anurag Varma]

8i onwards:
select
case
when y like '%A%' then 'XXXXX'
else 'BLAH'
end as y
from xx

I think DECODE is going to be deprecated in future versions.
A boring way to do this via decode will be:
select decode(instr(y,'A',1),0,'BLAH','XXXXX') from xx;

Anurag

"Ryan Gaffuri" <rgaf...@cox.net> wrote in message news:1efdad5b.03061...@posting.google.com...

[Ryan]

"Anurag Varma" <av...@hotmail.com> wrote in message
news:jBQFa.1071$1i6...@news02.roc.ny.frontiernet.net...

ahhh.... case statements. I thought I was going to have to do something with
nested translates and all that stuff. talk about blowing up my code.

i never saw the 8i docuemtnation for case statements. its exactly the same
syntax as 9i right?

thanks.

[Nuno Souto]

rgaf...@cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.03061...@posting.google.com>...

>
> ME>select decode(y,'%A%','XXXXX') from xx;
>
>
> DECOD
> -----
>
>
> 1 row selected.
>
> ME>select decode(y,'%A%','XXXXX','BLAH') from xx
> MASTER_TEST2@BOBAIRD>/
>
> DECOD
> -----
> BLAH

Would:
select decode(instr(y,'A'),0,'BLAH','XXXX') from xx;
do what you looking for? Or vicky-the-versa on the
decode result strings?

Cheers
Nuno Souto
wizo...@yahoo.com.au.nospam

[http://www.webyourcompany.com]

SQL> create table xx(y varchar2(20));

Table created.

SQL> insert into xx values('ABCD');

1 row created.

SQL> select case when y like '%A%' then 'XXXX' else 'BLAH' end FROM xx;

CASE
----
XXXX

SQL> select decode(y,'%A%','XXXXX','BLAH') from xx
2 /

DECOD
-----
BLAH

HTH,
http://www.webyourcompany.com

rgaf...@cox.net (Ryan Gaffuri) wrote in message news:<1efdad5b.03061...@posting.google.com>...

[Ganesh Raja]

Case Can be Used here ...

Case When y Like '%A' Then 'XXXX' Else 'YYYY' End;

--
HTH

Regards,
Ganesh R

"Ryan Gaffuri" <rgaf...@cox.net> wrote in message
news:1efdad5b.03061...@posting.google.com...

[Ryan Gaffuri]

thanks

select decode(instr(y,'A'),0,'BLAH','XXXX') from xx;

I dont quite follow the syntax. Why is there a '0'?
Also I would think that 'XXXX' would be the 'else' and the 'BLAH'
would be the then.

So if pattern 'A' exists then 'BLAH' else 'XXXX', but it seems to be
the other way around?

wizo...@yahoo.com.au (Nuno Souto) wrote in message news:<73e20c6c.03061...@posting.google.com>...

[Norman Dunbar]

Hi Ryan,

DECODE(expression, value_1, result_1, ....., default result)

so :

if the expression "instr(y,'A')" returns zero, do a BLAH else do an
XXXX.

The problem is 'the wrong way round' because you only know that zero
means 'not present in string' and any other result means 'present in
string'. So if A is not present in the string, do BLAH otherwise do
XXXX.

See ?

Cheers,
Norman.

-------------------------------------
Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman...@LFS.co.uk
Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com
-------------------------------------