When attempting to perform fuzzy matching on address keys:
| 1. | Use the SORTNORMALIZE() function because it performs all of the NORMALIZE() harmonizations, plus it re-orders words and numbers in descending order to increase the chances of matching (for example: SOUTH BROADWAY WEST vs. BROADWAY SOUTH WEST would both be rendered as WEST SOUTH BROADWAY for comparison purposes) |
| 2. | Apply a tailored substitution file to the SORTNORMALIZE() function that removes unnecessary address information, and standardizes common street abbreviations (like ST for STREET, BLVD for BOULEVARD) and references (like PO, PO BOX, BOX, or APT as UNIT) |
Notice that the substitution file entries shown above are upper cased and are missing any punctuation as abbreviations (replacements or removals) are performed only after the SORTNORMALIZE() function first upper cases the data and removes any punctuation. For more information, see SORTNORMALIZE().
The substitution file is stored in the project folder and is a text file that can be easily created using NOTEPAD. For more detailed information on constructing an substitution file, see NORMALIZE().