CODEWITHSUNDEEP
mysql
Reado
Reado

Reputation: 1452

MySQL search for keywords containing apostrophe or not

I have a MySQL database containing a list of UK towns and one of which is "Connah's Quay".

I want to be able to return results for "Connah's Quay" whether I have used the apostrophe or not, so "Connah's Quay" and "Connahs Quay" returns "Connah's Quay".

Rather than creating a field containing both versions (one with and another without the apostrophe), is there a SQL query I can run that will return results whether I have used the apostrophe or not?

QUERY:

SELECT * FROM uk_postcodes WHERE postal_town LIKE "connahs%";

Upvotes: 0

Views: 222

Answers (2)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

You might try this:

SELECT * 
FROM uk_postcodes 
WHERE REPLACE(postal_town,"'","") LIKE CONCAT(REPLACE("connah's","'",""),"%");

This removes the apostrophes from both the search term and the column value before the comparison.

Upvotes: 0

Rowland Shaw
Rowland Shaw

Reputation: 38130

Standard approach would be to normalise the data and search on that, so something like:

SELECT * FROM uk_postcodes WHERE REPLACE(postal_town, '''', '') LIKE 'connahs%';

This is a bit horrible to do on the fly (and not index friendly), so you would be better to store on table (also means you can also then cope with "Stow-cum-Quy" vs. "Stow cum Quy", etc.)

Upvotes: 1

Related Questions