Is there any easy way to compare LIKE Addresses from one table, which contains 3rd party data, to another table, our database source

We have a 3rd party that is supplying us data and we need to compare the addressing between the 3rd party data to our source database addressing. I'd like to make it somewhat flexible meaning I'd like to somehow use the LIKE comparison rather than comparing the exact address values. (I have noticed that the 3rd party addressing sometime has a leading <space> at the beginning of the address...why I'd prefer to use LIKE)

Is there any easy way to do this? Or does this dictate using a CURSOR and processing through the CURSOR of 3rd party data and plugging in the address LIKE as dynamic SQL?

Please let me know your thoughts on this and I appreciate your review and am hopeful for a reply.

March 24th, 2015 1:46pm

If you're worried about a leading whitespace, use LTRIM().


Free Windows Admin Tool Kit Click here and download it now
March 24th, 2015 1:57pm

Unfortunately it's more than that....I just want to try and be as flexible as possible comparing their address values with ours...
March 24th, 2015 2:08pm

Don't use a cursor... Is there a way to do this? - Yes. But you need to be a bit more specific on what "flexible" means. What are your requirements? For example, give us something like, I want to compare the values in this scenario:

1. Where address numbers are the same

2. Where street name and state are the same

3. etc, etc.

There are any number of ways to accomplish what you are asking.

Free Windows Admin Tool Kit Click here and download it now
March 24th, 2015 2:20pm

This is the reason you'll pay big bucks for software to clean up your postal addresses :)

It's not easy.

March 24th, 2015 2:25pm

Yes, it's possible and there are a variety of options but it's may not be for the faint of heart.

The last time I did it, I ended up taking several passes at the data.

1st pass was a straight up comparison with no modifications or functions.

2nd pass was the same but with all special characters removed.

3rd pass involved splitting the numeric portion of address and comparing just the street numbers and used a double meta-phone function (kind of like a soundex on steroids) to compare the street names.

Free Windows Admin Tool Kit Click here and download it now
March 24th, 2015 2:33pm

Is a leading <space> the only difference between addresses in those two tables?

You can give a better presentation with some sample data which cover all difference scenario.
March 25th, 2015 2:42am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics