Compare two fields for full or partial match

[PDAnalyst]

Hi,

I am using CR 2013 and connecting to a MS SQL 2014 with Windows 7 workstation.

I am looking to see if there is anyway to compare two separate address fields and determine if there is an exact match , close match (70% and above) and no match.

As an example, I would like to see the "flag" formula populated according to the match rate like below:

Address 1 ______ Address2 ______ Flag
123 E Main St ... 123 E Main St .... Exact
123 Main St ...... 123 E Main St .... Partial
123 Main ......... 123 E Main St ..... Partial
123 Main Ave ... 123 E Main St .....Partial
524 Main Ave ... 123 E Main St .... No Match
123 East St ..... 123 E Main St ..... No Match

(I had to put the periods since otherwise it did not display correctly, in the report, there are no periods)

The reason is that I have field personnel that are completing the Address 1 field with erroneous data and I have office personnel that verify the address in Address 2 field which is highly accurate

If anyone has any ideas to tackle this, I will appreciate it

 

[kray4660]

I know that there is a soundex function in 2011. I have never used it, but I think that might be the only way to go.

 

[SkipVought]

You need to be careful because it's possible that you could have valid differences like...

123 E Main St -- 123 W Main St

BTW, use TGML PRE tags to post your tables like...

[pre][ignore][pre][/ignore]
Address 1 Address2 Flag
123 E Main St 123 E Main St Exact
123 Main St 123 E Main St Partial
123 Main 123 E Main St Partial
123 Main Ave 123 E Main St Partial
524 Main Ave 123 E Main St No Match
123 East St 123 E Main St No Match
[ignore][/pre][/ignore][/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PDAnalyst]

Thanks kray4660. I will look at it which might be a viable option.

SkipVought, thanks for the pre tag.

we usually do not have problem of different E and W since my dispatchers have a geoverification server when the call comes in, they accurately place the address. It is my Police Officers that forget to put the prefixes or they decide to shorten the address or change the street type.

Safa,

 

[HowardHammerman]

The soundex function looks at a string of text. It eliminates all the vowels (except for the first letter) and one of all the duplicated consonants. Then it assigns a number to each consonant or consonant group according to the sound. It returns the first letter and the first four numbers regarding the sounds.
b, f, p, v → 1
c, g, j, k, q, s, x, z → 2
d, t → 3
l → 4
m, n → 5
r → 6


Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com