Solved

MySql Boolean Search on #/+ special characters

Posted on 2011-09-23
7
497 Views
Last Modified: 2012-06-27
We need to be able to search for the + sign and # sign in MySQL using MATCH AGAINST IN BOOLEAN MODE, but these characters are not apparently searchable by default, which means we cant search for c++ or c# in boolean mode.  I have gone and read the MySQL documentation on this matter but to be honest, its not in laymen's terms and I am shocked by the lack of information on this subject, I would have thought this would have been a common requirement.

I have also just found out, that if we want it to search for + and # and use UTF8 that we have to modify the source code for mysql!!  Surely this cant be true?  Im sure someone out there knows of a solution to this, there must be, other MySQL is apparently not the right choice here.
0
Comment
Question by:recruitit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 500 total points
ID: 36585489
Sounds like you are building a search engine for a jobs site ? =).  Anyway, no, MySQL full text search is still very much lacking in terms in functionality and performance.  Suggest you look into Sphinx Search (http://sphinxsearch.com/) and other 3rd party search engines.
0
 

Author Comment

by:recruitit
ID: 36585548
ok, thanks for the feedback, im going to have to write a quick temporary hack for converting it into a bunch of LIKES with wildcards, which im sure isnt going to be greatly performant considering the possible complexity lol, but changing the database isnt really a feesable option at the moment, will have to plan for the future there!  Thanks again
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36585549
If you still want to try MySQL full text, somebody said this in http://stackoverflow.com/questions/584976/how-do-you-get-your-fulltext-boolean-search-to-pick-up-the-term-c, don't know if it actually works:

From http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html:

"A phrase that is enclosed within double quote (“"”) characters matches only rows that contain the phrase literally, as it was typed."

This means you can search for 'C++' using this query:

SELECT * FROM mytable WHERE MATCH (field1, field2, field3) AGAINST ('"C++"' IN BOOLEAN MODE)
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:recruitit
ID: 36585571
oh nice, ill have a look
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 36585612
Definitely using LIKE is a performance killer.  I'd rather do some other hack like convert "+" to "plus" when stored to the database and also when made part of a search criteria.  I think your best bet is still to use something like Sphinx - that will take some work, but far less work than changing databases.
0
 

Author Comment

by:recruitit
ID: 36585712
sorry searching by phrase doesnt work :( thanks though
0
 

Author Comment

by:recruitit
ID: 36585748
ok, we are going to use the conversion method, that will be ok for awhile, thanks!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question