Filtering results using LIKE

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

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

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. '%'));