List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 12 1999 1:03pm
Subject:Re: Regex in mysql
View as plain text  
On Thu, 1999-08-12 15:16:27 +0300, Sinisa Milivojevic wrote:
> Richard Ellerbrock writes:
>  > I have a huge table (around 500000 records and growing) and
>  > another small tables containing regular expressions. Using the
>  > regex table, I group records in the big table and count them
>  > up. My select looks as follows:
>  > 
>  > select regerr.message, count(err.message) as num
>  >   from err, regerr
>  >   where err.message rlike regerr.message
>  >   group by regerr.message
>  > 
>  > This works just fine and shows all records that matches something
>  > in the regex table. Now I want to do the converse - find all
>  > records that do NOT match the regex table.
[...]
> May be I have not understood you well, but would NOT RLIKE do what
> you want ????

Not quite, because this yields all regular expressions which happen to
fail for anyone of the messages, i.e. that don't match always.

I think what Richard needs is this query:

  SELECT err.message, COUNT(*) AS num
  FROM   err LEFT JOIN regerr ON err.message REGEXP regerr.message
  WHERE  regerr.message IS NULL
  GROUP BY err.message;


A more simple example to illustrate what's happening:

   Table a    Table b 
   +------+   +------+
   | x    |   | y    |
   +------+   +------+
   | test |   | ^t   |
   | auto |   | t    |
   | haus |   | t$   |
   | wort |   | au   |
   | mies |   +------+
   +------+

   mysql> select * from a,b where x NOT REGEXP y ORDER BY x,y;
   +------+------+
   | x    | y    |
   +------+------+
   | auto | t$   |
   | auto | ^t   |
   | haus | t    |
   | haus | t$   |
   | haus | ^t   |
   | mies | au   |
   | mies | t    |
   | mies | t$   |
   | mies | ^t   |
   | test | au   |
   | wort | au   |
   | wort | ^t   |
   +------+------+

   mysql> select * from a,b where x REGEXP y ORDER BY x,y;
   +------+------+
   | x    | y    |
   +------+------+
   | auto | au   |
   | auto | t    |
   | haus | au   |
   | test | t    |
   | test | t$   |
   | test | ^t   |
   | wort | t    |
   | wort | t$   |
   +------+------+

   mysql> select * from a LEFT JOIN b ON x REGEXP y ORDER BY x,y;
   +------+------+
   | x    | y    |
   +------+------+
   | auto | au   |
   | auto | t    |
   | haus | au   |
   | mies | NULL |
   | test | t    |
   | test | t$   |
   | test | ^t   |
   | wort | t    |
   | wort | t$   |
   +------+------+
   
   mysql> SELECT * FROM a LEFT JOIN b ON x REGEXP y
      --> WHERE y IS NULL ORDER BY x;
   +------+------+
   | x    | y    |
   +------+------+
   | mies | NULL |
   +------+------+

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Regex in mysqlRichard Ellerbrock12 Aug
  • Regex in mysqlsinisa12 Aug
    • Re: Regex in mysqlMartin Ramsch12 Aug
Re: Regex in mysqlRichard Ellerbrock12 Aug