Geek asks and answers

MySQL - search for a field that ignores spaces.

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

Example:

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

Real world example:

My client has a website that sells long block engines. However, they often force people to search on their site for “cat 3306 longblock ” (instead of “cat 3306 long block”). We need mechanisms with long blocks that will be displayed when searching for a "long block".

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

+5
source share
3 answers

Sphinx Lucene , , mysql. , , mysql, .

, , , , .

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

, , LIKE "% term%".

, , , . , . OR . , , "cat 3306 longblock", product_name_search "3306catlongblock". 3306 long-block :

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

, freetext, .

+2

, , , . , , http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

, i.e.

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

There is a way to use the repace function. Example:

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

More articles:


All Articles