"Robert Brown" <robertbrown1971@yahoo.com> wrote in message
news:240a4d09.0404301119.467bd1e1@posting.google.com...
> I have researched newsgroups and the web very thoroughly and
> unsuccessfully for a solution to what I believe is a very common
> problem. I know it's easy to do wildcard match against data in DB
> (using LIKE and "%" and "?").
>
> But is it possible to match a concrete string against a database of
> wildcarded data? ("%" and LIKE do not work). For example:
>
>
> CREATE TABLE blacklist (
> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
> pattern VARCHAR(255) NOT NULL
> /* ... */
> );
>
> INSERT INTO blacklist (pattern) VALUES ('%foobar.com');
>
> Is there a select query that would match address mars2.foobar.com
> against this row?
>
> Some people claim that the following query will work. I have tried it
> and it's not true on either Oracle or SQL server.
>
> SELECT * FROM blacklist WHERE 'mars2.foobar.com' LIKE pattern;
>
> Some people suggest breaking up the blacklist table into N varchar
> fields for each domain segment and then representing a wildcard
> character as a NULL and use isNull to match it. This does work to an
> extent. However, a) it seems really ugly, b) does not allow arbitrary
> wildcarding (eg %mars%foobar.com), and c) this is something the DB
> should do out of the box.
>
> Please help! Humanity will be greatful as there's currently no
> solution to this anywhere on newsgroups.

> Works fine for me on Oracle:
>
> C:\>sqlplus
> Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
>
> SQL> create table blacklist (pattern varchar2(255));
>
> Table created
>
> SQL> insert into blacklist (pattern) values ('%foobar.com');
>
> 1 row inserted
>
> SQL> select * from blacklist where 'mars2.foobar.com' like pattern;

"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns94DC31A1B00CYazorman@127.0.0.1...
> David Best (davebest@usa.net) writes:
> > Works fine for me on Oracle:
> >
> > C:\>sqlplus
> > Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
> >
> > SQL> create table blacklist (pattern varchar2(255));
> >
> > Table created
> >
> > SQL> insert into blacklist (pattern) values ('%foobar.com');
> >
> > 1 row inserted
> >
> > SQL> select * from blacklist where 'mars2.foobar.com' like pattern;
>
> And the same example (save the funny varchar2) works on MS SQL Server too.
>
> And should work on about any DBMS, as this is core SQL.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

> It should work, but the query is going to be very inefficient.

Erland Sommarskog wrote:
>
> Jim Kennedy (kennedy-downwithspammersfamily@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 ('%@example.com')
> go
> SELECT pattern FROM blacklist
> WHERE reverse('spammer@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@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp