Joining tables on values that don't match

Question

I have two tables like this:

Summary Table

ID int
CityName Varchar(200)
PopulationCount int

Data Table

ID int
CityName Varchar(200)
PopulationCount int
Longitude float
Latitude float

The summary table has 800 rows and the DataTable has 800,000 rows. Each CityName in the Summary Table is in the Data Table at least once (and quite often multiple times). I need to create a new table to hold each of the 800 cities from the summary table and their corresponding Latitudes/Longitudes from the Data Table.

The problem is the CityName field in the summary table isn't an exact match to the CityName in the Data Table. There are slight differences.

   Sample Data: 
   Summary Table, CityName: Yarmouth (N.S.)
   Data Table, CityName: Yarmouth

The pattern in the sample data above doesn't always hold true i.e. the difference is not always a bracket so I dont see regex etc working. I imagine a contains() or like '% CityName %' could possibly work but I don't know how to implement them correctly.

Any help would be greatly appreciated...

Answer 1

Using 'like' would look something like this:

select * 
from Summary s
     join Data d on (s.CityName like '%' + d.CityName + '%') or (d.CityName like '%' + s.CityName + '%')

But like you said, that may not capture everything

Answer 2

I am not sure if this would be the best approach, but given it's a one-off job, as you confirmed in a comment, I would definitely try the following:

1. Create an intermediate table CityNames with columns like this:

   • StateCode (your state code),

   • CityNameVariant,

   • CityNameProper.

2. Populate the first two columns with all the distinct combinations of StateCode and CityName found in both source tables.

3. Using a series of manual updates, fill the CityNameProper column with names stripped off the various non-relevant parts like (N.S.) or , N.S. etc., so as to get a single uniform name matching its various versions, for every city. So the table would contain rows like this:

   StateCode  CityNameVariant  CityNameProper
   ---------  ---------------  --------------
   NS         Yarmouth (N.S.)  Yarmouth
   NS         Yarmouth         Yarmouth
   NS         Yarmouth, N.S.   Yarmouth
   

   Update statements might look like this:

   UPDATE CityNames
   SET CityNameProper = SUBSTRING(
     CityNameVariant,
     1,
     CHARINDEX('(', CityNameVariant)
   )
   WHERE CityNameVariant LIKE '% (%)'
   ;
   UPDATE CityNames
   SET CityNameProper = SUBSTRING(
     CityNameVariant,
     1,
     CHARINDEX(',', CityNameVariant)
   )
   WHERE CityNameVariant LIKE '%, %'
   ;
   UPDATE CityNames
   SET CityNameProper = SUBSTRING(CityNameVariant, …, …)
   WHERE CityNameVariant LIKE '…'
   ;
   

4. The final query matching the two original tables would then look something like this:

   WITH joined AS (
     SELECT
       s.StateCode,
       s.CityName,
       d.PopulationCount,
       d.Longitude,
       d.Latitude,
       rnk = ROW_NUMBER() OVER (
         PARTITION BY s.StateCode, s.CityName
         ORDER BY d.Population  /* or maybe ‘BY Longitude, Latitude’
                                   or ‘BY (SELECT 1)’, it may not matter
                                   but in any event it's up to you */
       )
     FROM Summary s
       INNER JOIN CityNames c ON s.StateCode = c.StateCode
                             AND s.CityName  = c.CityNameVariant
       INNER JOIN Data d      ON d.StateCode = c.StateCode
                             AND d.CityName  = c.CotyNameProper
   )
   --INSERT INTO SummaryWithData
   SELECT
     StateCode,
     CityName,
     PopulationCount,
     Longitude,
     Latitude
   FROM joined
   WHERE rnk = 1
   

That is, match all the cities in Summary with those in Data and pick for each in the former one row from the latter.