We have a table in which one of the columns is described as number. The users who access this table makes queries via a GUI. The query is defined as i like-query since they often search for substrings.

The users sees the value with preceeding zeros. Of course it should be defined as varchar in the database, but that leads to incompability with other systems in the organization and so the column is defined as number.

Here is an example:
select * from table_a where col1 like '000037%'

Oracle treats '000037%' as character, ignores the index and do not get any hit in the database (we don't get the answer we expect).
How can we force Oracle to handle '000037%' as number?


[Edited by jenlin on 10-10-2001 at 05:40 AM]