Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Reverse Wildcard Searches Impossible?

Reverse Wildcard Searches Impossible?

From: Robert Brown <robertbrown1971_at_yahoo.com>
Date: 30 Apr 2004 12:19:57 -0700
Message-ID: <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. Received on Fri Apr 30 2004 - 14:19:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US