search canadian province

Question

Hi,

I have an input search on the site and I want to search in the database (MySQL) after province name.

In database I have just the provinces initials - ex: QC for Quebec, AB - Alberta, etc.

The people can introduce into the input search field: QC or PC or Province du Québec or Province Quebec or Quebec or Québec (capital letters or not) (of course - this is just for one of the province, but are more :) )

What kind of SQL statment I can use or how can I solve my problem?

Thanks

Answer 1

You need an alias table where you can define all the synonyms for a given province (ie. alias). Each alias would have a FK to the province it belongs. You can then do your search against the alias table and do a join to your province table to find whatever province.