Re: Problem with translate function in whereclause.

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 translate( x,
 '*?',
>> '%_' );
>>
>> X
>> -------------------------
>> Hello*World
>> Hello_World
>> H*o_W*_
>>
>>
>>
>> Can you provide a testcase for us that shows the problem?
>>
>> --
>> Thomas Kyte (tkyte@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@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