Apostrophe's Issue

[crosescu]

Hi all,

I store all the restaurant names in the database that contain an apostrophe like this:
"John's Restaurant" is stored in the MySql database like "John's Restaurant"

But when people search for the restaurant without the apostrophe "Johns Restaurant" then the restaurant "John's Restaurant" is not matched/returned.

Is there a way to get around this problem?

[joelgreen]

try removing s from the end of the word by php. You could also perform search as it is (Johns Restaurant), but provide a link like this, clicking on it would perform another search:
Did you mean "John's Restaurant"?

This is the way Google works. Try entering some misspelled word and perform search

[dreamcatcher]

You could also use REPLACE [dev.mysql.com] to strip the apostrophe when you query the database.

SELECT * FROM table WHERE REPLACE(name,'\'','')' LIKE '$name';

Think that might work ok.

dc

[stevedob]

From that same page, you could also use SOUNDEX?

[joelgreen]

>> You could also use REPLACE to strip the apostrophe when you query the database.

if so you'll need:
1) save without quotes in database
or
2) remove quotes in SQL statement when comparing.

#2 is slow since it will not be able to use index

#1 would work fast but problems if you output the same field to user (it would be without quotes). Maybe save both texts, one with stripped ' for search, one non-stripped for display

[joelgreen]

SOUNDEX could match wrong entries, like "Jon" or "Joon" would match "John". A kind of unexpected result.

[stevedob]

You'd use it in the 'did you mean?' sense, if you hadn't got an exact result returned. Some (even possibly bizarre) results are going to provide a better user experience than just returning nothing.