0

I have two tables of addresses that I am trying to match to. Both are structured with Street (including number), city, state and zipcode. In my query, I am trying to join the two tables Like this:

JOIN [AxSupport].[dbo].Address ADR ON LPA.City = ADR.City 
   and LPA.State = ADR.State 
   and ADR.ZipCode = LPA.ZIPCODE 
   and ADR.COUNTRY = LPA.COUNTRY
   and ADR.Street  = LPA.STREET

I tested with one customer that has 2020 address records records and the joined result only returns 79 records. If I change the street comparison to Soundex(ADR.Street) = SoundEx(LPA.Street) I get 2660 records. Is there another way of formatting the streets to get an accurate return?

Dale K
  • 25,246
  • 15
  • 42
  • 71
tjturney
  • 1
  • The values are not the same. For example, `St` is not the same as `Street`. If you need to compare this way, you need to normalize your addresses. – Joel Coehoorn Jul 07 '20 at 22:49
  • This is a tricky business requirement. You have to decide what is a match for your requirements, and then post some sample data to obtain help with making the match. – Dale K Jul 07 '20 at 22:49
  • 1
    Address matching is a slippery slope. Normalization is the way to go here. You would be surprised on the variations of Boulevard alone. Take a peek at https://stackoverflow.com/questions/41249742/address-standardization-within-a-database/41249971#41249971 – John Cappelletti Jul 07 '20 at 23:14
  • Normalisation is best, but next best is probably a lookup table of abbreviations/misspellings. – Dale K Jul 07 '20 at 23:30
  • I sort of agree with John, but the terminology is "standardization". You need to standardize the addresses -- and you can find vendors of such software or services on the web. – Gordon Linoff Jul 08 '20 at 00:27
  • Thanks everyone for your responses. I'm going to work with the AR team to see what we can do to clean these up. – tjturney Jul 08 '20 at 16:25

0 Answers0