Solved

MySql Boolean Search on #/+ special characters

Posted on 2011-09-23
7
494 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

776 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