I have a query that returns user information, which includes city and country. The problem is users have entered their city names into a text field, so the formatting differs.
Washington D.C.
Washington, D.C.
Washington, DC
New York
NY
Is there anyway to determine that "New York" and "NY" are the same city? I don't need to use a group by in my query, the cities can be parsed in PHP.
EDIT: I should have mentioned that the damage is done, I inherited this database with 15,000 user records. Cities are entered through some corporate profile anyway.