MYSQL, ignore whitespace, punctuation, substitute accents in query

I need a way to use my dictionary with province abbreviations, full nam开发者_开发百科es and territories without having to add an entry for every possible misspelling of Ontario. Table is as such:

state       |name     | territory
===================================
AB           Alberta   West

Data is as such:

"Ontario,"
"ON,"
"ON"
"On"
" Ontario"
"Ontario "
" Ontario "


Quebec
Québec
QU
QB
Quebec,

---

not a real answer for you immediate problem, but this is what normalization is for. make another table for provinces, and then link that to the original table instead of allowing freeform text.

alterantely, add a trigger tot he input to enforce some consistency to the data collected. (at least trim the spaces, make it all UPPER, maybe check for valid entries.. etc.)

---

Just an idea, knowing it can't be used for all your db.
Anyway: what about using
SELECT * FROM table WHERE name LIKE '%ontario%'?

---

A bit late for the OP, but for others trying to do loose match searches, check out STRCMP() and SOUNDEX() as detailed here:

http://www.madirish.net/node/85