October 24, 2013 at 8:45 am
Hey guys,
I have two tables that I need to compare and grab all the matching records that are in both tables...
However, one table is very inconsistent, for example, like Address, it may have the word Road spelled out but in the next record, it may just be Rd...So I am running into problems getting an accurate number because in Table A the address could be 123 Circle Drive but for the same person in Table B, the address is 123 Circle Dr...Therefore that would not show up as a match...
Is there any way I can compare records and if they are say 60% match, I can pull that one?
October 24, 2013 at 8:54 am
Is this a one-time thing or an on-going process that you need to develop? Is the issue always as simple as Drive - dr., Lane - ln., etc.? Or is there other issues like Smith - Smyth and/or misspellings too?
October 24, 2013 at 9:21 am
Its a one time thing now, but its possible there will be a need for later...
That is the most common example...
The request I got was to compare two addresses and if the addresses are at least a 60% match, then that can counted as a match...
October 24, 2013 at 9:40 am
I'm not sure what 60% means to you but you have a couple of possibilities:
1. If this is on-going then look into DQS: http://technet.microsoft.com/en-us/library/ff877917.aspx
2. You could try using SOUNDEX http://msdn.microsoft.com/en-us/library/aa259235%28SQL.80%29.aspx
a. Here is an example:
select soundex('Drive'), SOUNDEX('Dr')
and the results:
D610 & D600 (very close)
3. Create a temp table to load the data into and update the addresses to use the correct canonical USPS abbreviations and then run your compare. i.e. so Street, st, str, etc. all become ST.
October 24, 2013 at 9:42 am
Thank you for your responses! I will look into these and see if they help me!
October 24, 2013 at 10:43 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply