Fuzzy Matching: 9999123456799 to 9912345679999

[bari1975]

I am trying to do fuzzy matching between two data fields that each have 13 digits.  I want the ranges to match if a lower set of consecutive digits, like the 7 numbers bolded in my example below.    

I want my logic to match the following two fields.

9999123456799   matches to   9912345679999    I want the fuzzy logic to notice that the string of digits "1234567" are in the same order and match these two fields.  

Thanks for the help

[MarqueeCrew]

While I'm looking into this, I did want to point out that 9912345 also matches as well as 9123456 and 3456799.  

Are there special rules for 0 and/or 9?

Are you looking for matches of any 7 digit sequence only?

For your example, a TRIM function TRIM[Field],'9') would get you where you wanted.  Please let us know your sequence matching rules.

Thanks,

Mark

[bari1975]

That is a good point about the matching i didn't even notice they matched when I wrote my question.  The only issue with Trip is that sometimes i need to match on 7 digits or 8 .. or 9 for example.

[bari1975]

I ment to say Trim

[MarqueeCrew]

I've got a module that looks for 7 or 8 digit fuzzy duplicates.  The output for 9999123456799 matching to 9912345679999 contains:

1234567,
12345679,
2345679,
23456799,
3456799,
9123456,
91234567,
9912345,
99123456

I think that there are more requirements to be discovered .....

[bari1975]

That might be a start for what i need to do.  can you post the module?  I think i might be able to use it.

[MarqueeCrew · Accepted solution]

Here is my approach:

Since we're looking at a set of 2 fixed 13 byte fields I will break them into all 7 and 8 digit possible sequences.  I'll then compare the values for equivalence and output the duplicates.  I'll use Transpose tools to create rows of data (Key of RECORD ID) and look for duplicate sequences.  Once a duplicate sequence is found, you can use the record ID to flag the record).

Does that solve your immediate need?

Thanks,

Mark