DIFFERENCE() performs a “fuzzy” comparison of two strings and returns a positive whole number as a result.
By default the comparison is case-sensitive. Specifying F as the third parameter renders the comparison case-insensitive.
The DIFFERENCE() function measures the Damerau-Levenshtein distance between two strings.
Strings that are identical will always have a difference of zero, whereas strings that are very similar will have a very small difference relative to their overall lengths. Strings that are totally different will have a difference equal to the length of the longer string.
When performing difference comparisons on strings, the NEAR() and SIMILAR() functions are significantly more efficient than the companion DIFFERENCE() function. For more information see NEAR() and SIMILAR().
DIFFERENCE(C1, C2 <,F>)
| • | C1 is the first string being compared |
| • | C2 is the second string being compared |
| • | F is an optional parameter that will render the comparison case-insensitive |
DIFFERENCE('abc', 'abcd') = 1
DIFFERENCE('abcdef', bcdefg') = 2
DIFFERENCE('abcd', 'efghij') = 6
DIFFERENCE('abcd', 'abcd') = 0
DIFFERENCE('abc', 'DBE') = 3
DIFFERENCE('abc', 'DBE', F) = 2
When looking for similar strings, you may want to remove case as an issue. To do this you can use the UPPER() or LOWER() functions:
IF DIFFERENCE(UPPER(surname),"SMITH")<2
Additionally, you may want to exclude relevant punctuation (periods, commas and hyphens) and blanks spaces as an issue. To do this specify:
IF DIFFERENCE(EXCLUDE(UPPER(surname),”.,- “),"SMITH")<2