Punctuation insensitive search in mySQL

Question

I have a database of phrases that users will search for from their own input. I want them to find the phrase regardless of what punctuation they use. For example if the phrase, "Hey, how are you?" is in the row, I want all of the following searches to return it:

• "Hey! How are you?!"
• "Hey how are you?"
• "Hey :) How are you?"

Right now, I have the columns 'phrase' and 'phrase_search'. Phrase search is a stripped down version of phrase so our example would be 'hey-how-are-you'.

Is there anyway to achieve this without storing the phrase twice?

Thank you!

-Nicky

Answer 1

What you've done is probably the most time-efficient way of doing it. Yes, it requires double the space, but is that an issue?

If it is an issue, a possible solution would be to convert your search string to use wildcards (eg. %Hey%how%are%you%) and then filter the SQL results in your code by applying the same stripping function to the database input and the search string and comparing them. The rationale behind this is that there should be relatively few matches with non-punctuation characters in-between the words, so you're still getting MySQL to do the "heavy lifting" while your PHP/Perl/Python/whatever code can do a more fine-grained check on a relatively small number of rows.

(This assumes that you have some code calling this, rather than a user typing the SQL query from the command line, of course.)