[thelist] MySQL REGEXP Help

[kasimir-k]

E Michael Brandt scribeva in 2008-02-15 02:26:
> Interesting.  I do not understand why mine fails with #1 and #3 yet 
> works fine with #6.  My experience is with javascript really, not so 
> much with mySQL.

> r937 wrote:
>> id  foo                       michael  rudy   
>>  1  the moon.The dish            0       1    
>>  2  one thing leads to another   0       0    
>>  3  one thing.another            0       1    
>>  4  curly, larry, and moe        0       0    
>>  5  shemp, joe,and curley joe    0       1    
>>  6  one.two                      1       1    
>>  7  one!three                    0       1    

It's because MySQL regular expressions are a bit different to Javascript 
ones. In MySQL '\.\w' matches to any character followed by a letter 'w'.

"To use a literal instance of a special character in a regular 
expression, precede it by two backslash (\) characters. The MySQL parser 
interprets one of the backslashes, and the regular expression library 
interprets the other." <http://dev.mysql.com/doc/refman/5.0/en/regexp.html>

Also, in MySQL '\w' does not mean a word character - character classes 
are dealt with [[::]]. So 'one.two' matches because there a 'w' is 
preceded by a character.

For more details have a look at regex man 
<http://www.tin.org/bin/man.cgi?section=7&topic=regex>

.k