Oracle numeric string column and indexing

Question

I have a numeric string column in oracle with or without leading zeros samples:

00000000056
5755
0123938784579343
00000000333  
984454  

The issue is that partial Search operations using like are very slow

select account_number from accounts where account_number like %57%

one solution is to restrict the search to exact match only and add an additional integer column that will represent the numeric value for exact matches.
At this point I am not sure we can add an additional column,
Do you guys have any other ideas?

Is it possible to tell Oracle to index the numeric string column as an integer value so we can do exact numeric match on it?

for example query on value :00000000333
will be:

select account_number from accounts where account_number = '333'

While ignoring the leading zeros.
I can use regex_like and ignore them, but I am afraid its going to be slow.

Answer 1

Oracle supports function based indexes. SO if you index the result of to_number:

CREATE INDEX acocunt_number_idx 
ON accounts(TO_NUMBER(account_number))

The way, if you use a query with to_number for the exact numeric value, the index will be used:

SELECT account_number 
FROM   accounts
WHERE  TO_NUMBER(account_number) = 333

Answer 2

You can use to_number.

select account_number from accounts 
where to_number(account_number) = 333