MySQL - Search a field ignoring spaces

Question

What would be a good way to search a field, ignoring spaces in the search term AND the field?

Example:

SELECT * 
FROM tablename
WHERE NOSPACES(fieldname) LIKE '%{search term with spaces removed}%'

Real-world example:

My client has a website selling long block engines. However, they often get people searching on their site for "cat 3306 longblock" (instead of "cat 3306 long block"). We need the "long block" engines to show up when "longblock" is searched.

Note: it probably doesn't matter for purposes of this question, but this website is built using PHP 5.3 and phpActiverecord.

Answer 1

You may also want to consider Sphinx or Lucene for full text like searches over mysql's fulltext. I have not used them personally but I hear both are superior to mysql's built in fulltext indexes.

One thing to note with all of these methods is that they require a special index to keep updated which adds more maintenance and server overhead.

Good article on sphinx: http://astellar.com/2011/12/replacing-mysql-full-text-search-with-sphinx/

For a no maintenance solution that does well for product name or numbers is just to split the user's search terms by spaces and add LIKE '%term%' for each to your query.

Additionally, I create a special search field that is the product name or number with its tokens alphabetized and spaces and symbols stripped. You need to update this field every time the product name changes. Then in addition to queries against the original field I OR search against this new stripped field. Example if you have a product name like 'cat 3306 longblock' then create a product_name_search field and place '3306catlongblock'. When a user searches for 3306 long-block You process the search by stripping symbols and splitting on spaces:

WHERE (other product name search clauses) OR 
    (product_name_search LIKE '%3306%' AND product_name_search LIKE '%longblock%')

This process is not as good as freetext but it does reasonably well for product names and numbers.

Answer 2

I would suggest there are a number of approaches, depending on how simple you want to make it. I personally would use Full-Text Search using the principles set out here http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

But you could also strip the spaces out of the search term using a regular expression i.e.

$str=preg_replace('/\s+/', '', $str);

Answer 3

There is a way using repace function. Example:

SELECT * FROM `products` where REPLACE(PartName, ' ', '') = REPLACE('0 1 3000 70 27', ' ', '')