views:

413

answers:

1

I've got a website for which I just wrote a great search function. I just realized that I have some words in my db with accent marks. So when somebody types in the word to search for, without the accent mark of course, they don't find what they are looking for.

most search functions have solved this problem by now; how do they do it? There must be some clever trick to it. Most of my queries use mysql's MATCH feature but one of them uses LIKE.

+1  A: 

You need to set a particular collation on your schema so that MySQL knows which characters are supposed to be equivalent. Whether a letter with a diacritical mark is considered the same as one without or some other sequence of characters is language-dependent, but try utf8_general_ci as a starting point for ignoring accents (assuming you're using a UTF-8 character set, which you should be really).

Try to use the same collation on all your strings, so that they can be joined and compared without expensive, index-breaking conversions.

bobince
This is absolutely correct, though it has often perturbed me how MySQL wraps character sets and collation up in one messy ball. The two are very different, and MySQL muddies the water like crazy with it.
Eric