List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:August 24 2001 1:40pm
Subject:Re: "not regexp"
View as plain text  
At 9:24 AM -0400 8/24/01, Network wrote:
>Would someone explain what is going wrong here.
>obviously I'm not understanding something.
>If the next query searches for patterns at the beginning of the string in a
>column
>mysql> select count(*) from a where addy1 not regexp
>'^(apt)|^(rm)|^(room)|^(ste)|^(suite)|^(fl)|^(floor)';
>+----------+
>| count(*) |
>+----------+
>|    80537 |
>+----------+
>
>and the following searches for the patterns at the *beginning* of 
>the string in
>a column -and- *anywhere* in the string of a column
>mysql> select count(*) from a where addy1 not regexp
>'^(apt)|^(rm)|^(room)|^(ste)|^(suite)|^(fl)|^(floor) or
>(ap)|(apt)|(rm)|(room)|(ste)|(suite)|(fl)|(floor)';

You can't OR patterns like that.  Perhaps you mean

WHERE NOT (addy1 REGEXP first-pattern OR addy1 REGEXP second-pattern)



>+----------+
>| count(*) |
>+----------+
>|    78088 |
>+----------+
>1 row in set (36.06 sec)
>
>and lastly;  the next line should find the pattern anywhere within 
>the string of
>the column....
>how in the hell is the next lines numeric results, less than the two 
>(2) queries
>above?
>I would think the query below would find all instances of those patterns and
>yield the most numeric results.
>mysql> select count(*) from a where addy1 not regexp
>'(ap)|(apt)|(rm)|(room)|(ste)|(suite)|(fl)|(floor)';
>+----------+
>| count(*) |
>+----------+
>|    77852 |
>+----------+
>
>The way I understand/mis-understand the three queries, is as follows
>
>where addy1 not regexp '(ap)|(apt)|(rm)|(room)|(ste)|(suite)|(fl)|(floor)';
>(should find the most)
>-
>where addy1 not regexp '^(apt)|^(rm)|^(room)|^(ste)|^(suite)|^(fl)|^(floor) or
>(ap)|(apt)|(rm)|(room)|(ste)|(suite)|(fl)|(floor)';
>(redundant but, should find the same numeric amount as the line above)
>-
>where addy1 not regexp '^(apt)|^(rm)|^(room)|^(ste)|^(suite)|^(fl)|^(floor)';
>(I'm like lost on this one)  results above.
>
>
>---------------------------------------------------------------------
>Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
>
>To request this thread, e-mail <mysql-thread83538@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-paul=snake.net@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-- 
Paul DuBois, paul@stripped
Thread
"not regexp"Network24 Aug
  • Re: "not regexp"Paul DuBois24 Aug