Oracle, Problem with translate function in whereclause.

Problem with translate function in whereclause.

Author	Message
Munic #1 / 5	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

Thomas Kyt #2 / 5	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 ------------------------- HelloWorld Hello_World Ho_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

Munic #3 / 5	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 > ------------------------- > HelloWorld > Hello_World > Ho_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

Thomas Kyt #4 / 5	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),'?','%_') ^^^^^ - Hide quoted text - - 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 >> ------------------------- >> HelloWorld >> Hello_World >> Ho_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

Munic #5 / 5	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 > >> ------------------------- > >> HelloWorld > >> Hello_World > >> Ho_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

Page 1 of 1

[ 5 post ]