Matching similar city names in SQL

Question

I have a table "City" which contains city names, and I have a another table which I just created and contains cities from different sources. When I run a query to match the cities between the two tables I find about 5000 mismatches.

So please give some queries which I can use to match cities (because sometimes users enter city names with one or two character different)... I have created a query which is working fine but I need such a query to match more.

Please suggest me what to do in such a situation.

SELECT distinct hsm.countryname,co.countryname,hsm.city,co.city
FROM   HotelSourceMap AS hsm
INNER  JOIN 
    (  SELECT c.*,cu.countryName
       FROM   city c
       INNER  JOIN  country cu ON c.countryid= cu.countryId
    ) co
ON (charindex(co.city,hsm.city) > 0 AND hsm.countryid = co.countryid) AND
    hsm.cityid is null

Answer 1

If you implement the Levenshtein Distance algorithm as a user-defined function, it will return the number of operations that need to be performed on string_1 so that it becomes string_2. You can then compare the result of the Levenshtein Distance function against a fixed threshold, or against a percentage length of string_1 or string_2.

You would simply use it as follows:

WHERE LD(city_1, city_2) < 4;

Using Full-Text Search may be another option, especially since an implementation of Levenshtein Distance would require a full table scan. This decision may depend on how frequently you intend to do this comparison.

You may want to check out the following Levenshtein Distance implementation for SQL Server:

• Levenshtein Distance Algorithm: TSQL Implementation

Answer 2

You must fix the names in the database. Databases are meant for exact matches, not "looks mostly like". The most simple fix is probably to export the table in a CSV format, load it in Excel (two columns: Primary key and city name) and then use a spell checker to fix the names. After all names have been fixed, import the table again.

Answer 3

You could use Soundex to compare two strings that are spelt different but have a similar pronounciation.

It depends how they are misspelt. If it is just typos, probably use Levenshtein Distance that Daniel Vassallo recommends. If it is misspellings by people who weren't sure how the city was spelt, use Soundex.

Maybe use both!

Answer 4

The best solution is to use SOUNDEX. I tried some test: It matches Waterland, Witerland but not Wiperland. I think this should fulfill your requirements. SOUNDEX converts an alpha string to a four-character code to find similar-sounding words or names.

select * from HotelSourceMap where SOUNDEX([city]) = SOUNDEX('Waterland')

==> Match

select * from HotelSourceMap where SOUNDEX([city]) = SOUNDEX('Witerland')

==> Match

select * from HotelSourceMap where SOUNDEX([city]) = SOUNDEX('Wiperland')

==> No Match

Answer 5

The SoundEx function would be the best option for such scenarios, but only works when the vowels in a word are incorrect or absent. If the consonants mismatch, it would not work. Another approach to do this would be to write a simple logic of defining the appropriate mismatch limit between two words; though would not a give 100% accuracy, might solve the purpose. A simple scalar valued function which uses the SoundeEx function - internally, should be sufficient enough.

Answer 6

I've had good luck with the Double Metaphone algorithm for fuzzy matches on names. The concept is similar to Soundex in that it boils a word down to a code, but it's much more sophisticated. In my database, I'll have a 'name' field and a 'nameDoubleMetaphone' field that I compute on insert. This makes searches and joins pretty quick.

Wikipedia is a good place to start: http://en.wikipedia.org/wiki/Double_Metaphone