Matching similar city names in SQL
select, sql, sql-server
Solution
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
Problem
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 ```