How to Escape MySQL REGEXP Strings in PHP
It’s usually weird with MySQL in everything I guess. Again, I ran into using Regex in MySQL using REGEXP which is pretty cool.
MySQL escape strings with double backslash \\, unlike other programming languages using single slash. But the problem here is that the string to be escaped is in a PHP variable, how do I escape it? preg_quote uses single slash.
Figured this out; I had to escape all non word characters using preg_replace which goes:
<?php
function mysql_regex_escape( $str ) {
return preg_replace( '/([\W])/', '\\\\\\\\$1', $str );
}
echo mysql_regex_escape( "..foo, bar//" );
This escapes strings for MySQL. You can now run to test:
SELECT CAST( 'this is a ..foo, bar// test' AS BINARY ) REGEXP '$str_var'
🙂
James John
Software Engineer
