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.



[Edited by nealh on 10-10-2001 at 09:25 AM]