Ignoring special characters in search query

[c73mr0ck]

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"

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

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:

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:

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?

 

[IanWaterman]

I would start by removing the OR and use a nested Replace

lower(replace(replace(replace(replace(name, ' ',''), ',',''), '&',''), '-','')) like '%globalhistorygeography%'

Also your first replace

replace(name, ' ','')) removes all spaces so you have to use like '%globalhistorygeography%'

Ian

 

[c73mr0ck]

Ian - thank you for your help and I took your suggestion and now my query is looking like this and returning a LOT more results:

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, you got me to the point where I'm getting acceptable results. Now it's down to knocking out the edge cases

 

[IanWaterman]

Filter you data for this specific book and add the replace to your select and see what is returned

eg

select replace......
from products
where name = 'Lippincott's Q&A Review for NCLEX-RN, North American Edition'

Ian