Re: Problem with translate function in whereclause.

That works excellent.

Thank you for your time.

Matt

"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:9j9mf301kq9_at_drn.newsguy.com...
> In article <9j9hhv$135j$1_at_msunews.cl.msu.edu>, "Munich" says...
> >
> >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
> >
>
> the CHAR type is stored blank padded so, that is like:
>
> where 'WHITEBOARD' like '% '
>
> which doesn't match.
>
> Instead, you can:
>
> where 'WHITEBOARD' like translate (rtrim(lot),'*?','%_')
> ^^^^^
>
>
> >
> >
> >
> >"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
 ranslate( 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
> >>
> >
> >
>
> --
> 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 Mon Jul 23 2001 - 21:03:03 CEST