matching similar addresses

[TheShabz]

Hi all, a bit of a pickle I'm in.



I've been given a list of addresses provided by a vendor and I need to attach our account numbers to them. However, their address does not exactly match our address. there may be typos, there may be the street type (avenue, street, place, etc) attached to it and not always in spelled out or abbreviated (some say street, some say st). and then on top of that, not all of them have the street type. Our db separates all parts of the address, so I'm not finding a way to find a perfect match to create a join. I have nothing relevant to join on at the moment.

My plan of attack was to pull all our customers in the cities which the vendor provided addresses in and figure out a way to match as many as i can. Is there a way to match similar entries? For example, if one contains "maple street" and the other "Mapel st" i want it. i'm using the Like operator so far but with no join, 11k addresses provided, and a few hundred thousand in those service areas..... well, yea.

help?

if there was an icon for "crying my eyes out" i would have used it.

thanks everyone

[weekend00]

You can do it manually, one by one, this can keep you job for a few years

eh, just joking.

you can update all "st" or "st." to "steet", save to a temp table if you don't want change the original table. then you can match them.

you need to decide your rules of updating, for example, do you want "Maple street" match "Maple ave"? or only "street" = "st" or "st." ?

[TheShabz]

i need "Maple Street" to match:
maple
maple st
maple st.
maple street
maple str

[weekend00]

update all of them and then match them

[TheShabz]

but i dont know if the ones i listed are all the ones they have. they could have typod some of the names, like the "mapel st". they might have "maplest." its a really really bad list. One of the addresses they provided was "1/8 mile north of highway 60". needless to say, they wont be getting a match for that one.

[weekend00]

For those ones, you must do it manually.
Or after you finish the most part, you try to match with "maple*".