Solved

MySql Boolean Search on #/+ special characters

Posted on 2011-09-23
7
491 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
  • 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now