#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    17
    Rep Power
    0

    Ignore White spaces


    Hi All,

    I am using 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;
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by deepak.fugo
    I have written the query as below but i need to ignore white spaces while matching, how can we achieve it
    please explain which white spaces you need to ignore
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    17
    Rep Power
    0
    Originally Posted by r937
    please explain which white spaces you need to ignore
    Thx for reply

    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 want the result to be matched.

    i.e. "Tooth brush" => Value present in Keyword Column
    if User search "toothbrush" then i should get the result.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    you're on the right track with using REPLACE

    however, MATCH will then be unable to use the fulltext index... which necessitates the use of IN BOOLEAN MODE... which will be extremely slow

    i'm not sure you're going to be successful in your attempts...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    17
    Rep Power
    0
    Originally Posted by r937
    you're on the right track with using REPLACE

    however, MATCH will then be unable to use the fulltext index... which necessitates the use of IN BOOLEAN MODE... which will be extremely slow

    i'm not sure you're going to be successful in your attempts...
    Thanks for the Reply...
    Can u pls suggest any other method where i can match keyword and value of the Columns (like keyword column) ignoring white space.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by deepak.fugo
    Can u pls suggest any other method
    i would already have done so
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo