How can multiple characters be considered equivalent?

[old.rhodesiascout]

I have made a search engine on my site and it searches a MYSQL database.Usually searches are case-insensitive, that is when you search for "cars", it can return the results "Cars" or "CARS" or "caRs" etc. This is fine.

I however have two different languages in my database, one being english and the other greek. In the greek language we use "accents" which is a little ' symbol above a letter to stress the pronounciation. Only the vowels have these accents and only sometimes(I mean not every vowel in the word will have a accent, just one in every word.)
Now when i search for a greek word lets say "cars" , it will not find it in the database if its spelt with an accent eg "c'ars"

How do i modify MYSQL so that it considers the letter " a " and the letter " 'a " to be equivalent in the searches as is the letter"a" and "A" ?
I do have the greek character set installed so i can see and write greek words into my db without a problem.It's just the searches is where i have a problem, (and here my problem only occurs if a word in the db has a accent in it.)

To explain a bit in english its like the english word "bite". The letter " i " has a dot over it. We dont type the dot over the "i" , it just happens automatically by the keyboard. Imagine though that you could type the dot over the "i" or leave it without a dot.
Now if in the db the word "bite" was with a dot and you searched for the word " bite" (without the dot) , it wouln't find it because it considers the "i" as a different character.You see?

I'd like to know how to make them equivalent for the searches.

 

[Teaser]

mySQL databases use ' symbols to de-limit fields. So if you want to enter data that contains a ' , you must convert the ' to another symbol/s first. When retrieving the data you do the opposite to get your symbol back to a '. In PHP I used the ereg_replace() function to do this, and it works fine.

But if the database is set up with Greek installed it might take care of this - but I doubt it since it doesn't with English etc.

That's all that comes to my mind.

...After looking at this again I think i'm may be completely wrong, but i'll keep the post up.

 

[old.rhodesiascout]

I dont think you're following me.
I dont have a problem entering data with an ' symbol over a letter nor do i have a problem with seeing data with an ' symbol over a letter .
My problem is searching for a word that has an ' symbol.
The catch is that the letter " a " and the letter " 'a " are regarded as separate characters. Its like me searching for the word " bite" and it thinks i mean " byte" .

Let me ask you this.
How is it when you search for a word like "cars" it can return you results like " CARS" or "Cars" ?

I want to be able to do the same thing.The same way as it regards the small letter "c" as being equivalent to the capital letter "C" , thats what i want it to do with "a" and " 'a " (and every other vowel that takes an "accent").
Do you know what i mean?
I am pretty sure it has to do with modifying character sets, but how do i do it?

 

[Speccy]

I suppose one way to do it would be to not send the search word directly into the SQL statement but have some code that detects special cases. ie if a user enters ca'rs you have a lookup table that appends WHERE field = cars OR field = ca'rs to the SQL.

Once the procedure is done changes only have to be made to the lookup table to make changes in the future.