Problem with translate function in whereclause. 
Author Message
 Problem with translate function in whereclause.

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*','*?','%_')



Tue, 06 Jan 2004 03:51:20 GMT
 Problem with translate function in whereclause.


Quote:

>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:


Table created.



1 row created.


1 row created.


1 row created.



'%_' );

X
-------------------------
Hello*World
Hello_World
H*o_W*_

Can you provide a testcase for us that shows the problem?

--

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



Tue, 06 Jan 2004 06:24:37 GMT
 Problem with translate function in whereclause.
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


Quote:

> >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:


> Table created.



> 1 row created.


> 1 row created.


> 1 row created.



'*?',
> '%_' );

> X
> -------------------------
> Hello*World
> Hello_World
> H*o_W*_

> Can you provide a testcase for us that shows the problem?

> --

> 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



Tue, 06 Jan 2004 23:13:33 GMT
 Problem with translate function in whereclause.

Quote:

>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),'*?','%_')
                                   ^^^^^

- Show quoted text -

Quote:




>> >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:


>> Table created.



>> 1 row created.


>> 1 row created.


>> 1 row created.



>'*?',
>> '%_' );

>> X
>> -------------------------
>> Hello*World
>> Hello_World
>> H*o_W*_

>> Can you provide a testcase for us that shows the problem?

>> --

>> 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

--

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


Wed, 07 Jan 2004 00:32:35 GMT
 Problem with translate function in whereclause.
That works excellent.

Thank you for your time.

Matt


Quote:

> >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),'*?','%_')
>                                    ^^^^^




> >> >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:


> >> Table created.



> >> 1 row created.


> >> 1 row created.


> >> 1 row created.



ranslate( x,
> >'*?',
> >> '%_' );

> >> X
> >> -------------------------
> >> Hello*World
> >> Hello_World
> >> H*o_W*_

> >> Can you provide a testcase for us that shows the problem?

> >> --

> >> 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

> --

> 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



Sat, 10 Jan 2004 03:03:03 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. The oracle translate function

2. Translate Function in Order By clause?

3. String substitution with "translate" function

4. translate function

5. translate function

6. H.E.L.P w TRANSLATE function

7. H.E.L.P w TRANSLATE function

8. TRANSLATE function doesn't work correctly?

9. - H.E.L.P w TRANSLATE function

10. Translating ORACLE Decode Function to Sybase

11. Translate Sybase Function

12. translate function


 
Powered by phpBB® Forum Software