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 -> Re: Reverse Wildcard Searches Impossible?

Re: Reverse Wildcard Searches Impossible?

From: David Best <davebest_at_usa.net>
Date: Fri, 30 Apr 2004 13:08:20 -0700
Message-ID: <pbudnX7Si76pLw_dRVn-ig@speakeasy.net>


"Robert Brown" <robertbrown1971_at_yahoo.com> wrote in message news:240a4d09.0404301119.467bd1e1_at_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;

PATTERN




%foobar.com Received on Fri Apr 30 2004 - 15:08:20 CDT

Original text of this message

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