Re: Reverse Wildcard Searches Impossible?

The query will still need a table (or index) scan, because the column is to the right of the LIKE operator...

Gert-Jan

Erland Sommarskog wrote:
>
> Jim Kennedy (kennedy-downwithspammersfamily_at_attbi.net) writes:
> > It should work, but the query is going to be very inefficient.
>
> Yes, if there is an index on pattern it is not going to be useful,
> since the match is at the end of the string. But that is not really
> the same that it is very ineffecient. If you have a million entries,
> you will certainly notice the toll. But with thousand? Not very much.
> And thousand is a more likely number than a million.
>
> For this particular case there exists a possible way to speed things up.
> Since we search for the end of the string, you could have:
>
> CREATE TABLE blacklist (pattern varchar(225) NOT NULL PRIMARY KEY,
> revpattern AS reverse(pattern));
> CREATE UNIQUE INDEX revix ON blacklist (revpattern);
> go
> INSERT blacklist VALUES ('%_at_example.com')
> go
> SELECT pattern FROM blacklist
> WHERE reverse('spammer_at_example.com') LIKE revpattern
>
> However computed columns is not standard SQL, and may not work on all
> DBMSs. The above works in SQL Server.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar_at_algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

-- 
(Please reply only to the newsgroup)