How to make MySQL treat underscore as a word separator for fulltext search?

Question

I am using MySQL fulltext and PHP (codeigniter) to search a database containing RSS items. Problem is some of these items's titles use underscores instead of spaces. Since MySQL considers underscores as part of a word, these items will never be matched in the search, unless the user types the exact title including underscores.

Server is shared so I don't have access to MySQL Server System Variables.

Can this behavior be changed in some other way? Can this maybe be done through the search query itself?

I know I could just replace all underscore occurrences in the DB by spaces, but this would compromise the original integrity of those titles though. Just wondering if there's another way of doing this.

Answer 1

I don't think this can be done without access to the server. The only way I have ever seen to do it is the first comment on this mySQL manual page ("How I added '-' to the list of word characters"). It requires stopping the server and changing internal configuration.

Your best bet is probably creating a second column with removed underscores, and to search that.

Answer 2

I know I could just replace all underscore occurrences in the DB by spaces, but this would compromise the original integrity of those titles though. Just wondering if there's another way of doing this.

You can instead of replacing underscores in original title field, use a separate field dedicated to fulltext searches.

This allows you to replace underscores, plus aggregates keywords into this field (category names, authors, tags, etc.) to enhance search results relevance. We used this a lot of times with success for getting rid of HTML tags in content infering with search