Search prob in mysql query

Question

Is there any way to search like below criteria in mysql? If any the pl reply.

if i search with "murray's" then it then it will return fine data for "murray's" but it should also return data for "murrays" means same word without apostrophy(').

same way if i search without apostrophy('), the it will search also with apostrophy(').

at last

if search query is "murray's", the it will return "murray's" and "murrays" also.

and

if search query is "murrays", the it will return "murrays" and "murray's" also.

Thanks in advance

Answer 1

Your best bet is to create a Full Text Index on your table.

You can then query it as:

select *
from restaurants
where match(name) against ('murrays')

Barring that, you can use SOUNDEX or SOUNDS LIKE in your query. The following two queries are exactly identical:

select *
from restaurants
where soundex(name) = soundex('murrays')

select *
from restaurants
where name sounds like 'murrays'

Also note that MySQL uses the original SOUNDEX algorithm, not the more recent one. Therefore, it will return arbitrary length strings. If you've used SOUNDEX before, just make sure you take that into account.