Ignore white space in MATCH AGAINST

[deepak.fugo]

Hi All,

I am uisng Mysql DB and i have a task for suggesting Categories based on User keyword input.
I have written the query as below but i need to ignore white spaces while matching, how can we achieve it

SELECT search.*,
MATCH (name) AGAINST ('black' IN BOOLEAN MODE) AS name_match,
MATCH (keywords) AGAINST ('black' IN BOOLEAN MODE) AS keyword_match,
MATCH (description) AGAINST ('black' IN BOOLEAN MODE) AS description_match
FROM search
WHERE MATCH (name, keywords, description) AGAINST ('black' IN BOOLEAN MODE)
ORDER BY (name_match * 3  + keyword_match * 2  + description_match) DESC LIMIT 0,100;

I tried using replace() which were throwing error

SELECT search.*,
    MATCH (replace(`name`,' ','')) AGAINST ('black' IN BOOLEAN MODE) AS name_match,
    MATCH (replace(`keywords`,' ','')) AGAINST ('black' IN BOOLEAN MODE) AS keyword_match,
    MATCH (replace(`description`,' ','')) AGAINST ('black' IN BOOLEAN MODE) AS description_match
    FROM search
    WHERE MATCH (name, keywords, description) AGAINST ('black' IN BOOLEAN MODE)
    ORDER BY (name_match * 3  + keyword_match * 2  + description_match) DESC LIMIT 0,100;

[icmkarthi]

where this this code to be used in which app.

Regards

[deepak.fugo]

I want it in my site. I am using PHP, Mysql and Apache web server.
What i want is to match the values of columns (name,keyword and description) ignoring whitespace

Ex. if my keyword column has "Tooth bursh" and User Inputs keyword "toothbrush" i will not get results if i run the above query

[pritaeas]

The problem with your query is that MATCH() has column names as parameters, and not the values from those columns. That's why the REPLACE() is not allowed.

It is very difficult to determine, if the user inputs "toothbrush", that you should also look for "tooth" and "brush". The only way to do that is to build a dictionary with related/similar words to be used in a join.