Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    16

    Unanswered: Ignoring special characters in search query

    've been wracking my brain trying to find a solution for this for a couple of days. I'm trying to make a "smart" query that can handle a wide range of search terms. The queries run fine until there are special characters involved and I've had some success w/ the REPLACE method on some characters such as commas and dashes. Other characters such as quotes and ampersands will result in empty queries.

    Here's a few examples:

    the original name I'm searching for is "French Is Fun, Book 1 - 1 Year Option" and with this query below, I get results returned with these search terms:

    1. "French Is Fun"
    2. "French Is Fun, book"
    3. "French Is Fun, book"
    4. "French Is Fun, Book 1"

    Code:
    SELECT * FROM `products` WHERE ( (LOWER(name) LIKE '%french is fun book%' OR
     LOWER(replace(name, '  ','')) LIKE '%french is fun book%' OR
     LOWER(replace(name, ' ','')) LIKE '%french is fun book%' OR
     LOWER(replace(name, '-','')) LIKE '%french is fun book%')
    However, when the original title has an ampersand in it like such: "Global History & Geography: The Growth of Civilizations - 1 Year Option" - I get an empty query when I try these different search terms:

    1. "Global History & Geography"
    2. "Global History Geography"

    I've tried this to no avail
    Code:
    SELECT * FROM `products` WHERE  
    	(LOWER(name) LIKE '%global history geograph%' OR  
    	 	LOWER(replace(name, '  ','')) LIKE '%global history geography%' OR  
    	 	LOWER(replace(name, ' ','')) LIKE '%global history geography%' OR 
    	 	LOWER(replace(name, ',','')) LIKE '%global history geography%' OR 
      		LOWER(replace(name, '&','')) LIKE '%global history geography%' OR  
    	 	LOWER(replace(name, '-','')) LIKE '%global history geography%');
    I also tried adding an escape character to the ampersand and it doesn't help:
    Code:
    SELECT * FROM `products` WHERE  
    	(LOWER(name) LIKE '%global history geography%' OR  
    	 	LOWER(replace(name, '  ','')) LIKE '%global history geography%' OR  
    	 	LOWER(replace(name, ' ','')) LIKE '%global history geography%' OR 
    	 	LOWER(replace(name, ',','')) LIKE '%global history geography%' OR 
      		LOWER(replace(name, '\&','')) LIKE '%global history geography%' OR  
    	 	LOWER(replace(name, '-','')) LIKE '%global history geography%');
    And commas in the name also return empty results. As a demonstration, the original name is this:

    "Amsco's AP Calculus AB/BC Preparing for the Advanced Placement Examinations - 1 Year Option"

    This attempt always returns empty queries:

    Code:
    SELECT * FROM `products` WHERE 
    	( (LOWER(name) LIKE '%amscos ap calculus%' OR
    		 LOWER(replace(name, ' ','')) LIKE '%amscos ap calculus%' OR
    		 LOWER(replace(name, '\'','')) LIKE '%amscos ap calculus%' OR
    		 LOWER(replace(name, ',','')) LIKE '%amscos ap calculus%' OR
    		 LOWER(replace(name, '-','')) LIKE '%amscos ap calculus%')
    		) AND ( (`products`.`type` = 'Rental' ) );

    Any ideas?

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    Clean up the query on the front end before you pass it as a search to the database.

  3. #3
    Join Date
    Aug 2004
    Posts
    16
    guelphdad - can you elaborate on that?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider using a fulltext search
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2004
    Posts
    16
    I already looked into it but all of our tables were converted to innoDB over a year ago and we aren't going back .

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So as alternative stuff the information that if needs the fulltext search (just that/the columns along with the pk) into a myisam table. All ge rest of the data for that entity remains inane I node table as does the rest of the applications data. Use view to read write as if its one table.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2004
    Posts
    16
    Just wanted to give an update:

    So far this version of my query is giving me the best results:

    Code:
    SELECT * FROM `products` WHERE (in_store = 1 AND (LOWER( replace(replace(replace(replace(replace(name, ' ',''), ',', ''), '&', ''), '-', ''), ':', '') ) LIKE '%globalhistorygeography%' OR LOWER(name) LIKE '%global history geography%') 
    )
    I'm still having some more "edge case queries that aren't returning results.

    For instance:

    This is the original title and if I use this query it returns results:

    "Lippincott's Q&A Review for NCLEX-RN, North American Edition"

    but, when I take out the ampersand, I don't get any results returned:

    "Lippincott's QA Review for NCLEX-RN, North American Edition"

    Thanks again for all the help - you've helped me get to the point where I'm getting acceptable results. Now it's down to knocking out the edge cases

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •