End of Line Matching Index

Question

I have a phone number table storing the phone number as varchar (20). I have a requirement to implement a search for integers, and only in the last part of the number, so a typical query would be:

SELECT * FROM PhoneNumbers WHERE Number LIKE '%1234'

How can I put an index in a column Numberto make these searches effective? Is there a way to create an index that sorts records on an inverted row? Another option may be to change the numbers before storing them, which will give queries such as:

SELECT * FROM PhoneNumbers WHERE ReverseNumber LIKE '4321%'

However, this will require that all database users always change the row. This can be solved by saving both the normal and the inverse number, and updating the number updated by the trigger when inserting / updating. But this 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 do not need users to access if they did not execute the query manually, and you can use the computed column rather than the 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*/