The SOUNDEX() function is often used to identify potential duplicates based upon the way ACL interprets the sounds of the letters being analyzed. For example, if you have the names John Smith, Jon Smith, Jhn Smith, Johnie Smith, and John Smyth you can create a SOUNDEX NAME using the command:

SOUNDEX( SPLIT( Name, " ", 1)) + SOUNDEX( SPLIT( Name, " ", 2))

After creating the SOUNDEX NAME, you can run a traditional duplicate search and confirm that these names have the same SOUNDEX NAME. Using the above examples, the command would create the following SOUNDEX NAMES:

NAME SOUNDEX NAME

John Smyth J500S530

Jon Smith J500S530

Jhn Smith J500S530

Johnie Smith J500S530

John Smith J500S530

While this appears to be a positive means to identify potential duplicates that are not perfectly identical, it has numerous problems.

First, see what happens when there are simple spelling mistakes:

NAME SOUNDEX NAME

Mohn Smith M500S530

John Dmith J500D530

Gohn Smith G500D530

Johns mith J520M300

Joh nsmith J000N253

SOUNDEX NAMES are overly sensitive to the first letter in a the cell.

Second, SOUNDEX only works on short words. It only analyzes the first few letters (about 4-6). After that, you can have COMPLETELY different words and they will "sound alike." For example:

NAME SOUNDEX NAME

GLENHAVEN G451

GLENFARBER G451

GLENBROOK G451

SUPERCALIFRAGILISTICEXPIALIDOCIOUS S162

SUPERCONDUCTOR S162

SUPPERS S162

According to the Soundex() function, these words sound the same and would be identified as potential duplicates.

Third, another problem with SOUNDEX() is that the above formula is designed to work with fields that have two segments. If you are dealing with a field with more than two segments you run into problems. For example, the above formula would produce the SOUNDEX NAME of J500D000 for the name "John D Smith." One could write a more elaborate script to look at 3 segments and end up with the SOUNDEX NAME of J500D000S530.

Fourth, SOUNDEX doesn't work with numbers. Number sequences will have the first number and then zeros following it. For example, SOUNDEX("1234976") will have a soundex name of 10000.

There are other ways to perform fuzzy matching that provide better results.