DICECOEFFICIENT( ) function

Returns the Dice's coefficient of two specified strings, which is a measurement of how similar the two strings are.

Syntax

DICECOEFFICIENT(string1, string2 <,ngram>)

Parameters

Output

Numeric. The value is the Dice's coefficient of the two strings, which represents the percentage of the total number of n-grams in the two strings that are identical. The range is 0.0000 to 1.0000, inclusive.

Examples

Basic examples

How the n-gram length affects the result

The three examples below compare the same two strings. The degree of similarity returned varies depending on the specified n-gram length.

Returns 0.9167 (using the default n-gram length (2), the n-grams in the two strings are 92% identical):

DICECOEFFICIENT("125 SW 39TH ST, Suite 100","Suite 100, 125 SW 39TH ST")

Returns 1.0000 (using an n-gram length of 1, the n-grams in the two strings are 100% identical):

DICECOEFFICIENT("125 SW 39TH ST, Suite 100","Suite 100, 125 SW 39TH ST", 1)

Returns 0.8261 (using an n-gram length of 3, the n-grams in the two strings are 83% identical):

DICECOEFFICIENT("125 SW 39TH ST, Suite 100","Suite 100, 125 SW 39TH ST", 3)

Field input

Returns the Dice's coefficient of each value in the Address field when compared to the string "125 SW 39TH ST, Suite 100" (based on the default n-gram length of 2):

DICECOEFFICIENT(Address,"125 SW 39TH ST, Suite 100")

Advanced examples

DICECOEFFICIENT("John Smith","Smith, John")

DICECOEFFICIENT("John Smith", EXCLUDE("Smith, John", ","), 1)

DEFINE FIELD Dice_Co COMPUTED DICECOEFFICIENT(Address,"125 SW 39TH ST, Suite 100")

SET FILTER TO DICECOEFFICIENT(Address,"125 SW 39TH ST, Suite 100") > 0.5

Remarks

When to use DICECOEFFICIENT( )

Use the DICECOEFFICIENT( ) function to find nearly identical values (fuzzy duplicates). You can also use DICECOEFFICIENT( ) to find values with identical or near-identical content, but transposed elements. For example:

• telephone numbers, or social security numbers, with transposed digits
• versions of the same address, formatted differently

How it works

DICECOEFFICIENT( ) returns the Dice's coefficient of the two evaluated strings, which is a measurement of the degree of similarity between the strings, on a scale from 0.0000 to 1.0000. The greater the returned value the more similar the two strings:

• 1.0000 – means that each string is composed of an identical set of characters, although the characters may be in a different order, and may use different case.

• 0.7500 – means the n-grams in the two strings are 75% identical.

• 0.0000 – means the two strings have no shared n-grams (explained below), or the specified length of the n-gram used in the calculation is longer than the shorter of the two strings being compared.

Usage tips

• Filtering or sorting Filtering or sorting the values in a field based on their Dice's coefficient identifies those values that are most similar to the comparison string.
• Case-sensitivity The function is not case-sensitive, so "SMITH" is equivalent to "smith."
• Leading and trailing blanks The function automatically trims leading and trailing blanks in fields, so there is no need to use the TRIM( ) or ALLTRIM( ) functions when specifying a field as a parameter.
• Removing generic elements The OMIT( ) and EXCLUDE( ) functions can improve the effectiveness of the DICECOEFFICIENT( ) function by removing generic elements such as "Corporation" or "Inc.", or characters such as commas, periods, and ampersands (&), from field values.

  Removal of generic elements and punctuation focuses the DICECOEFFICIENT( ) string comparison on just the portion of the strings where a meaningful difference may occur.

How Dice's coefficient is calculated

Dice's coefficient represents the percentage of the total number of n-grams in two strings that are identical.

Dice's coefficient is calculated by first dividing the strings being compared into n-grams. N-grams (also referred to as q-grams) are overlapping substrings, or overlapping character blocks, with a length of n. You can specify the length of n using the ngram parameter, or accept the default length of 2.

Two names divided into n-grams

Here are the names "John Smith" and "Smith, John D." divided into n-grams with a length of 2, and n-grams with a length of 3. Underscores indicate spaces. Internal spaces and punctuation are counted as characters.

The Dice's coefficient formula

Once the n-grams have been established for two strings being compared, the calculation is completed using the following formula:

• 2 x the number of shared n-grams / the total number of n-grams in both strings

Shared n-grams are n-grams that appear in both strings. For example, "ABC" and "BCD" share the n-gram "BC", assuming an n-gram length of 2 (AB | BC and BC | CD).

Examples of calculating the Dice's coefficient

The table below illustrates calculating the Dice's coefficient for the two strings, "John Smith" and "Smith, John D.", using different n-gram lengths.

Note that as the n-gram length increases for the same pair of strings, the Dice's coefficient value decreases, indicating less similarity. Although the strings remain the same, the criterion for similarity becomes stricter because dividing the strings into longer n-grams means that longer sequences of characters must match for an n-gram to qualify as shared.

Another way of thinking about this point is that the relative position of characters is weighted more heavily as you increase the n-gram length. By contrast, the relative position of characters is not considered when using an n-gram length of 1. Relative position refers to the position of characters in relation to one another, rather than to their absolute position within a string.

DICECOEFFICIENT( ) compared to ISFUZZYDUP( ) and LEVDIST( )

One of the key differences between the DICECOEFFICIENT( ) function and the ISFUZZYDUP( ) and LEVDIST( ) functions, which use Levenshtein distance, is that DICECOEFFICIENT( ) de-emphasizes or completely ignores the relative position of characters or character blocks in the two strings being compared. Relative position is significant in the functions based on Levenshtein distance.

Comparison values with transposition

If you are comparing strings such as addresses, in which entire elements might be transposed, DICECOEFFICIENT( ) might be a better choice. For example, the same address with the "Suite" element transposed is identified as highly similar by DICECOEFFICIENT( ), but highly different by LEVDIST( ):

Comparison values without transposition

If transposition is not an issue, LEVDIST( ) may give more useful results. For example, the same corporation name with different punctuation is identified as highly different by DICECOEFFICIENT( ), but highly similar by LEVDIST( ):