Converting between Regexp and MySQL LIKE

Question

I would like to accept conditions in a format that can be used by both PHP's preg_match and MySQL's LIKE operator.

The reason for doing this is that the conditions are used in two different parts of code, one where it's significantly faster to match the condition to the data, and the other where it's significantly faster to match the data to the condition.

I'd like to provide the ability for the user to give something like :

'hostname' 'match' '*.core-*-??.foo.com'

For MySQL it would use:

'%.core-%-__.foo.com'

for preg_match it would use something like:

'/.*\.core-.*-..\.foo\.com$/'

I'm sure someone has already come up with some way of allowing portable matches between these two systems.

The alternative is to use regular expressions in MySQL, but I'm not sure how good an idea that is from a scalability standpoint.

It removed the slashes and asterisks. There should be an asterisk between the --, which converts to % in MySQL and a dot and an asterisk in the regexp. The ? are converted to a slash and a dot in the regexp. — adama
– adama, Commented Jul 12, 2013 at 12:23
"The alternative is to use regular expressions in MySQL, but I'm not sure how good an idea that is from a scalability standpoint." - this is definitely the way to go, but neither it nor simple LIKE pattern matching can avoid table scans when the pattern begins with a wildcard (so both will entail full table scans and scale poorly). — eggyal
– eggyal, Commented Jul 12, 2013 at 12:52

Robin Castlin · Accepted Answer · 2013-07-12 12:54:49Z

2

Try using preg_escape:

$str_php_input = str_replace("\\*", ".*", preg_escape($str_input));

$str_mysql_input = str_replace("*", "%", $str_input);

Get a good grasp over what should be escaped with \ and what shouldn't and you will have it working nicely.

answered Jul 12, 2013 at 12:54

Robin Castlin

11k1 gold badge30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Converting between Regexp and MySQL LIKE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related