Index for wildcard match of end of string

Question

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?

Answer 1

ALTER TABLE phonenumbers ADD reverse_number AS REVERSE(number) PERSISTED

CREATE INDEX ix_phonenumbers_reversenumber ON phonenumbers (reverse_number)

SELECT  *
FROM    phonenumbers
WHERE   reverse_number LIKE '4321%'

Answer 2

You don't need to have the users reverse unless they are manually executing the query and you can use a computed column rather than a trigger:

CREATE TABLE TBL (TEL VARCHAR(20) NOT NULL)
ALTER TABLE TBL ADD TEL_REV AS REVERSE(TEL)
CREATE NONCLUSTERED INDEX IX_REVERSETEL ON TBL (TEL_REV) INCLUDE (TEL)

INSERT TBL SELECT '12345678'
    UNION SELECT '147258369'
    UNION SELECT '963852741'

--find nums ending in 5678
SELECT * FROM TBL WHERE TEL_REV LIKE REVERSE('5678') + '%' /*index seek*/