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?
I don't think there's any way you could get Oracle to treat '000037%' as a number. You might be able to convert it into a > or < query (which would do a range scan of the index), but you'd have to know the number of digits after the "37".
The best option might to use a function based index on the column. The function based index could do the lpad(to_char(num_column),...) and queries which used a leading portion of the field might use the index.
Alternatively, re-design the application. What exactly are the users looking for when they type in '0000037%' ? Is it some sort of department code, for example ? If so, you could add an extra column called department_code. When '000037%' is typed in, the query could then be converted into a more sensible one applied to the indexed department_code field.