Re: Reverse Wildcard Searches Impossible?

From: Erland Sommarskog <sommar_at_algonet.se>
Date: Sat, 1 May 2004 08:28:24 +0000 (UTC)
Message-ID: <Xns94DC6A48F577BYazorman@127.0.0.1>

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

Received on Sat May 01 2004 - 03:28:24 CDT