Ignoring special characters in search query

Dev Articles Community Forums Sponsor:

October 25th, 2012, 10:56 AM

clem_c_rock

Registered User

Join Date: Oct 2004

Posts: 21

Time spent in forums: 3 h 9 m 47 sec
Reputation Power: 0

Ignoring special characters in search query

I'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?

Ignoring special characters in search query

Developer Shed Advertisers and Affiliates