List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 15 2003 6:54pm
Subject:Re: Help with SQL statement?
View as plain text  
At 16:40 +0000 1/15/03, Jeff Snoxell wrote:
>Hi,
>
>I've got a varchar field in a mysql table that contains 'N' numbers 
>(each a maximum of 4 digits, not zero padded) separated by spaces 
>when more than 1 number is present.
>
>eg. field content might be any of the following
>
>''
>'123 4567 1234 45 3'
>'3'
>'3 4 6'
>
>I want to select only those records that do not contain a certain number.
>
>Problem I have with using LIKE "%MyNum%" is that obviously this is 
>going to match "3" against "123" which I don't want it to.
>
>Is there something I can use like Perl's "word boundary" pattern match code?
>
>My statement so far is:
>
>SELECT * FROM MyTable WHERE RefList NOT RLIKE "\bMyNum\b" # Where \b 
>is a word boundary

Yes, you can use REGEXP and the ugly [[:<:]] and [[:>:]] markers:

... WHERE RefList NOT REGEXP '[[:<:]]MyNum[[:>:]]' ...


>
>Many thanks,
>
>
>Jeff
>
>
>---------------------------------------------------------------------
>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-thread130049@stripped>
>To unsubscribe, e-mail <mysql-unsubscribe-paul=snake.net@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Thread
Help with SQL statement?Jeff Snoxell15 Jan
  • Re: Help with SQL statement?Csongor Fagyal15 Jan
  • Re: Help with SQL statement?Paul DuBois15 Jan
  • Re: Help with SQL statement?Salam Baker Shanawa15 Jan