INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Ignoring special characters in search query

Ignoring special characters in search query

(OP)
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?

RE: Ignoring special characters in search query

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

RE: Ignoring special characters in search query

(OP)
[s]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:

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

RE: Ignoring special characters in search query

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close