Query with 'LIKE' comparison questions

I'm having trouble creating a query that runs at anything more that a crawl here.

1. VarChar Column (acct_num) that contains account numbers which have leading zeros (input by users). The problem is that the users seem to have put varying numbers of leading zeros (one to eight). So account 1234 may be 0001234 or 000001234. The Column is a index (non-unique). I need to find any rows that have this account number, regardless of the number of leading zeros.
2. select <other columns> from table where acct_num LIKE '%1234'; Is slow as a dog. Of course a where acct_numb='0001234' will return right away.
3. I also tried to drop the leading zeros: select <other columns> from table where to_number(acct_num)=1234; But that is slow too.

Any other hints. I thought about creating another table, with a select that uses the to_number and also grabs the other columns I'm interested in. Then querying against that table.

Pete
pstryjew at att dot net Received on Tue Oct 11 2005 - 14:58:04 CDT