Ignore white space

[deepakfugo]

Hi All,

I am uisng PHP & Mysql DB for my site 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 of the values of columns while matching, how can we achieve it

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

I tried using replace() which were throwing error

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

[robert_gsfame]

Have you tried TRIM() ??

[deepakfugo]

yes tried trim() also, not working