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*','*?','%_') Received on Thu Jul 19 2001 - 14:51:20 CDT