Using the Preg PCRE library in MySQL


#1

I’m having trouble figuring out how to use the Preg library for using Perl compatible regular expressions with MySQL.

I’ve been trying to test it in Python with MySQLdb by calling the ‘PREG_CHECK’ function with a trivial regex (“a”) but I keep getting a ProgrammingError telling me to check my query syntax which I’m assuming means the engine isn’t finding the function I’m trying to call. I’ve tried both all caps and lower case versions of the function and enclosing the regexp in quotes, forward slashes or nothing except the parentheses. For example c.execute(“PREG_CHECK(/a/)”), where c is a valid connection to my database, returns: _mysql_exceptions.ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘PREG_CHECK(/a/)’ at line 1”)
What am I missing?

Is the issue that this library doesn’t come pre-installed? (Some old threads implied that people were using it which was I was hoping it was pre-installed.) If so, any advice on how to get it?

Thank you very much for any advice you may have.


#2

The PREG_CHECK() function is not available on our MySQL servers — it’s a component of the nonstandard lib_mysqludf_preg library, which we do not have installed. It’s possible that you’ve confused it with the similarly named PHP PCRE library, which contains a number of functions with similar names (e.g, “preg_match”).

MySQL has a built-in REGEXP (aka RLIKE) operator to perform regular expression matching. It’s not the same as PCRE, but should suffice for most purposes. Keep in mind that regular expression matching is very inefficient, though; avoid using it in queries that will be searching a large number of rows.