Fixing data inconsistencies I'm trying to analyze some data I have but there is a lot of inconsistencies in my data.

I have a SQL table that I'm trying to analyze.

The table is a table of universities with the following structure: name:string, city:string, state:string, country:string
Name is always present however city, state, country can be missing.
My main issue is that there are a ton of typos and different declination of a university name.
For example here are the declination of Standford Unversity I find when I do  SELECT "universities".* FROM "perm_universities" WHERE (name like '%stanford%'):

stanford university - stanford - ca - united states of america
the leland stanford junior university - stanford - ca - united states of america
leland stanford jr. university - stanford - ca - united states of america
stanford university graduate school of business - stanford - ca - united states of america
the leland stanford junior university (stanford university) - stanford - ca - united states of america
leland stanford junior university - stanford - ca - united states of america
stanford university - stanford -  -
leland stanford jr. university, graduate school of business - stanford - ca - united states of america
stanford law school - stanford - ca - united states of america
stanford - stanford - ca - united states of america
stanford university, graduate school of business - stanford - ca - united states of america
stanford graduate school of business - stanford - ca - united states of america
stanford univerity - stanford - ca - united states of america
stanford university (the leland stanford junior university) - stanford - ca - united states of america
the leland stanford jr. university - palo alto - ca - united states of america
leland stanford junior university, school of law - stanford - ca / n/a - united states of america
stanford universit - stanford - ca - united states of america
the leland stanford university - stanford - ca - united states of america
leland standford stanford junior university - stanford - ca - united states of america
stanford university - cambridge - ma - united states of america
the leland stanford junior university 'stanford university' - stanford - ca - united states of america
stanford university school of law - stanford - ca - united states of america
stanford univresity - stanford - ca - united states of america
the leland stanford jr. university (stanford university) - stanford - ca - united states of america
leeland stanford junior university - stanford - ca - united states of america
leland stanford junion university -  - ca - united states of america
leland stanford junior university (stanford university) - stanford - ca - united states of america
the leland stanford junior university - stanford -  -
stanford university - graduate school of business - stanford - ca - united states of america
graduate school of business, stanford university - stanford - ca - united states of america
stanford universoty - stanford - ca - united states of america
leland stanford junior university - stanford -  -
stanford univeristy - palo alto - ca - united states of america
leland stanford university - palo alto - ca - united states of america
stanford university - stanford - ca / n/a - united states of america
the leland stanford junior university, stanford university - stanford - ca - united states of america
the leland stanford junior university graduate school of business - stanford - ca - united states of america
stanford universtiy - stanford - ca - united states of america
stanford univerisity - stanford - ca - united states of america
stanford university - stanford - ct - united states of america
stanford law scool - stanford - ca - united states of america
mba: stanford university - stanford - ca - united states of america


They are all the same university, but some have typos, some have different names, some have no cities, some have the wrong cities, ... The data isn't great.

So I'm trying to fix it. How can I consolidate this data?
Since this dataset is already organised in a table, you can leverage standard SQL functions to perform a large part of the cleanup.
A record seems to be composed of 4 fields, for example:

university name, city, state, country
stanford law school - stanford - ca - united states of america


You could follow these steps to get a cleaner representation of this dataset:


Starting with the highest level (country), find unique values use it to identify all similar sounding words by listing soundex matches with itself (build a join query of the table with itself).
Use these suggested similar matches to fix all mistakes by updating the names.
Continue in this manner till you've fixed all four fields.
Identify missing states by using the city name to query the correct state from the rest of the table; if state and country are missing for "leland stanford junior university", then use the city name "stanford" to join it with itself and get the state/country name from the other records in the table.
For the university name, identify all abbreviations using grep to search for words ending in a dot character. Replace them with full expansions.
Next, breakup the university name into individual words and dump these into a temporary table in a single column. De-duplicate the values in the column so it only contains unique values.
Run the same soundex matching join query as in step 1 to identify similar sounding names, append these suggested similar names in a second column of the temp table.
Manually do a quick sanity check of what you've obtained as suggestions and delete any invalid matches.
Write a procedure to replace the words in each university name with suggested replacements, and you will have a much cleaner dataset.