Comparing and quantifying address differences

• SQL33

  SSCrazy

  Points: 2044

  More actions

  July 1, 2011 at 8:57 am

  #99740

  I'm trying to determine code for comparing two address fields and returning a count of the differences between them... Tried using the difference command, doesn't work...

  Any suggestions would be appreciated. Total production table count is about 2000 records.

  IF OBJECT_ID('TempDB..#TBLADDR','U')is not null drop table #TBLADDR

  Create table #TBLADDR

  (Address1 Varchar (1024),

  Address2 Varchar (1024))

  SET DATEFORMAT YMD

  INSERT INTO #TBLADDR(Address1,Address2)

  SELECT '10205 NW HAMILTON PL','3044 ALORE CIR / CT D' UNION ALL

  select '10205 NW HAMILTON PL','3044 ALBORE CIR / CT D' UNION ALL

  SELECT '2024 ENETAL BEACH DR','2024 NE ENE LN' UNION ALL

  SELECT '9284 ONDA CIR','9284 ONDA CIR NW' UNION ALL

  SELECT '9529 SILALE WAY','9529 SILALE WAY NW' UNION ALL

  SELECT '7356 BRIDALE BLVD NW','7356 BRIDALE PL NW' UNION ALL

  SELECT '639 COWOOD DR','639 NE COWOOD DR' UNION ALL

  SELECT '85 SHORE PL','85 SHORE PL NW' UNION ALL

  SELECT '3063 ALBARE CIR','3063 ALBAE CIR / CT H' UNION ALL

  SELECT '1163 NE MCWILLMS RD','7372 BLACKBIRD DR NE' UNION ALL

  SELECT '7218 BRIDLELE BLVD NW','7218 BRIDLELE BV NW' UNION ALL

  SELECT '383 NW MON RD','383 NW MONYON RD' UNION ALL

  SELECT '6640 ARGYL CT','6640 ARGY CT NW' UNION ALL

  SELECT '75 HEN RD #C','75 HEN CT' UNION ALL

  SELECT '75 HEN RD #C','75 HEN CT' UNION ALL

  SELECT '2600 THENS WY UH1','2600 THENS WAY' UNION ALL

  SELECT '2600 THENS WY UH1','2600 THENS WAY' UNION ALL

  SELECT '6083 ICO WY NW','6083 ICO WAY NW' UNION ALL

  SELECT '3414 DRIDGE LN','3414 RIDGE LN NW' UNION ALL

  SELECT '3414 RIDGE LN','3414 RIDGE LN NW' UNION ALL

  SELECT '12447 NW HINGTON LP','12447 HINGTON LOOP NW'

  Select * From #TBLADDR

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  July 1, 2011 at 9:38 am

  #1347120

  Not sure if I understand your question correctly, but how about testing these:

  SELECT Id,Address1,Address2 FROM #Tbladdr WHERE RTRIM(Address1) <> RTRIM(Address2)

  SELECT COUNT(*) FROM #Tbladdr WHERE RTRIM(Address1) <> RTRIM(Address2)

  SELECT COUNT(*) FROM #Tbladdr WHERE RTRIM(Address1) = RTRIM(Address2)

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  July 1, 2011 at 9:57 am

  #1347138

  If you're after the number of character differences between two addresses you could use something like 'Levenshtein distance'. It's straightforward to implement as a SQLCLR - there is C code publicly available.

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  July 4, 2011 at 3:23 am

  #1347696

  Comparing strings may be VERY, let's say, misleading.

  Compare following 3 addresses:

  '7356 BRIDALE BLVD'

  '73/56 BRIDALE BLVD'

  'Apt. 73, Unit 56, BRIDALE Boulevard'

  Which ones will be closest by string comparison?

  And how does it correlate with real life?

  To compare addresses you parse the strings according to the local rules (better force formalized entry) and then compare addresses unit by unit. Appt.No to ApptNo, Street.No to StreetNo, Street Name to Street Name, etc.

  _____________
  Code for TallyGenerator