Re: anyone way to use a wildcard in a decode?

rgaffuri_at_cox.net (Ryan Gaffuri) wrote in news:1efdad5b.0306111155.78871856_at_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_at_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 Received on Wed Jun 11 2003 - 20:12:27 CDT