Filtering results using LIKE

Question

Consider these three "haystack" strings:

a) foo bar

b) welcome to foo bar industries

c) foo barer

And now my "needle":

foo bar

(Heh)

I would like my filter to match my needle with haystack strings a & b but not c. I have tried:

$collection->addAttributeToFilter('name', array('like' => '%'.$needle.'%'));

But the above matches with c.

I also tried:

$collection->addAttributeToFilter('name', array('like' => '% '.$needle.' %')); // Note the spaces

The above only matches with b.

Any help is much appreciated.

Answer 1

Give this a try and see if it fits:

$collection->addAttributeToFilter('name', array(
    array('like' => '% '.$needle.' %'), //spaces on each side
    array('like' => '% '.$needle), //space before and ends with $needle
    array('like' => $needle.' %') // starts with needle and space after
));

Passing the second parameter as an array of arrays will concatenate the conditions using OR

Answer 2

A possible solution is to use REGEXP instead of LIKE:

$collection->addAttributeToFilter('name', array('regexp' => '[[:<:]]'.$needle.'[[:>:]]'));

From the MySQL documentation:

[[:<:]], [[:>:]]

These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

Note that if $needle can contain characters that have special meanings in POSIX regular expressions, you need to escape them. See also: https://stackoverflow.com/questions/4024188/php-function-to-escape-mysql-regexp-syntax

Answer 3

The accepted answer doesn't work properly. It's checking only space before and after the text.

Suppose, you have following list

• Jacket
• Summer Jacket
• Winter Jacket

Now if you try to search with jacket using the accepted answer, it will return only Summer Jacket and Winter Jacket

I think following solution is better

$collection->addAttributeToFilter('name', array('like' => '%' . $needle. '%'));