Re: Problem with translate function in whereclause.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 19 Jul 2001 15:24:37 -0700
Message-ID: <9j7mn50ble@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, '*?', '%_' );

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

Received on Thu Jul 19 2001 - 17:24:37 CDT