Re: Problem with translate function in whereclause.

[Quoted] I think you just pegged it for me. We are not using VARCHAR2 fields, but CHAR fields. Here is my example (very similar to yours):

SQL> create table mam ( lot char(15) not null );

Table created.

SQL> insert into mam values ('WHI*');

1 row created.

SQL> insert into mam values ('WHITEBOARD');

1 row created.

SQL> insert into mam values ('*');

1 row created.

SQL> select * from mam where 'WHITEBOARD' like translate (lot,'*?','%_');

no rows selected

[Quoted] "Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:9j7mn50ble_at_drn.newsguy.com...
> In article <9j7dfn$2nat$1_at_msunews.cl.msu.edu>, "Munich" says...
> >
> >In my database, I have some fields that contain wildcard characters. Due
 to
> >the age of the database and its historical roots, the wildcard characters
 we
> >use are '*' and '?'. For Oracle, I need a select statement that will
> >effectively translate the '*' to a '%' and the '?' to a '_' in the where
> >clause. Take a look at this example:
> >
> >select record_id, data_field
> > from tablename
> > where constant like translate(tablename.data_field,'*?','%_')
> >
> >The constant is a non-wildcarded input value from the user.
> >
> >I cannot get this select to ever return me a row. However, if I replace
> >'tablename.data_field' with a literal string, then the select works fine:
> >
> >select record_id, data_field
> > from tablename
> > where constant like translate('data*','*?','%_')
> >
> >
> >
>
> I tried this in 7.3, 8.0 and 8.1 and it worked every time as expected:
>
> scott_at_ORA734.WORLD> create table t ( x varchar2(25) );
>
> Table created.
>
> scott_at_ORA734.WORLD>
> scott_at_ORA734.WORLD> insert into t values ( 'Hello*World' );
>
> 1 row created.
>
> scott_at_ORA734.WORLD> insert into t values ( 'Hello_World' );
>
> 1 row created.
>
> scott_at_ORA734.WORLD> insert into t values ( 'H*o_W*_' );
>
> 1 row created.
>
> scott_at_ORA734.WORLD>
> scott_at_ORA734.WORLD> select * from t where 'Hello World' like translate( x,
 '*?',
> '%_' );
>
> X
> -------------------------
> Hello*World
> Hello_World
> H*o_W*_
>
>
>
> Can you provide a testcase for us that shows the problem?
>
> --
> Thomas Kyte (tkyte_at_us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Fri Jul 20 2001 - 17:13:33 CEST