Search code examples
mysqlpunctuation

MYSQL, ignore whitespace, punctuation, substitute accents in query

I need a way to use my dictionary with province abbreviations, full names 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,
Solution

  • 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.)