Ed Leafe wrote:
Hi,
I'm trying to get a particular pattern to match, and a pattern that works in Python and other languages is not working in MySQL. I'm using 4.1.0 on RH Linux.
I archive messages from an email list, and we have a standard that posts to the list that are Off Topic should be labeled by including '[OT]' in the subject. However, some people are lazy, and type '(OT)', or '{ot}', etc. I'd like to be able to filter off-topic messages in the archives, and so a regexp that catches all these variants is what I'm looking for.
In some python scripts on the site, I use the following pattern: '[\[\(\{ ][Oo][Tt][ \]\}\)]'. That means an Open bracket/paren/brace or space, followed by 'ot' in either case, followed by a closing bracket/paren/brace or space.
Running this in Python, I get a match: >>> pat = '([\(][Oo][Tt][ \]\)])' >>> tx = 'This is an (OT) Test' >>> re.search(pat, tx).groups() ('(OT)',)
But in MySQL, I don't match: mysql> select 'This is an (OT) Test' regexp '[\(][Oo][Tt][ \]\)]' as mtch; +------+ | mtch | +------+ | 0 | +------+ 1 row in set (0.00 sec)
Can anyone see the problem here?
___/ / __/ / ____/ Ed Leafe
Linux Love: unzip;strip;touch;finger;mount;fsck;more;yes;umount;sleep
Unless your string column is defined as binary (or your mysql is 3.23.4 or earlier), pattern matching is case insensitive, so you can just look for OT instead of [Oo][Tt]. That's not the problem here, though.
According to the manual (<http://www.mysql.com/doc/en/Pattern_matching.html>
and <http://www.mysql.com/doc/en/Regexp.html>), you have to double your backslashes because of the way the pattern is parsed. I couldn't get that to work either, though. Maybe after my coffee kicks in...
In the meantime, perhaps the following will help:
mysql> select 'This is an [OT] Test'
regexp "[ [:punct:]]OT[ [:punct:]]" as mtch;
+------+
| mtch |
+------+
| 1 |
+------+
1 row in set (0.00 sec)Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
