Index for wildcard match of end of string
- by Anders Abel
I have a table of phone numbers, storing the phone number as varchar(20). I have a requirement to implement searching of both entire numbers, but also on only the last part of the number, so a typical query will be:
SELECT * FROM PhoneNumbers WHERE Number LIKE '%1234'
How can I put an index on the Number column to make those searchs efficient? Is there a way to create an index that sorts the records on the reversed string? Another option might be to reverse the numbers before storing them, which will give queries like:
SELECT * FROM PhoneNumbers WHERE ReverseNumber LIKE '4321%'
However that will require all users of the database to always reverse the string. It might be solved by storing both the normal and reversed number and having the reversed number being updated by a trigger on insert/update. But that kind of solution is not very elegant.
Any other suggestions?
