Escape special characters for SQL REGEXP

While using the MTG card search that I've been developing recently, I encountered an error that MySQL reported as follows:

Got error 'repetition-operator operand invalid' from regexp

The problem resulted from an unescaped { being passed to MySQL's REGEXP function. Because this character has a special meaning in regex, it must be escaped when it is being used literally within a string. This is done by preceding it with a backslash (\).

Developers will be familiar with using PHP's mysql_real_escape_string to escape problematic characters before submitting a query. When the query in question involves MySQL's REGEXP function, however, we need to go one step further and escape regex's special characters.

I had a look on php.net to see whether such a function exists. Having failed to find one there, I did a Google search but again came away empty-handed. I wrote my own function for the job, and thought I'd share it in case others encounter the same problem:

function mysql_regexp_escape_string($string)
{
    $special_chars = array('*', '.', '?', '+', '[', ']', '(', ')', '{', '}', '^', '$', '|', '\\');
    $replacements = array();

    foreach ($special_chars as $special_char)
    {
        $replacements[] = '\\' . $special_char;
    }

    return str_replace($special_chars, $replacements, $string);
}

It is quite possible that a solution to this problem already exists. If this is the case, I am eager to know about it.

Respond