An interesting and useful application of the NORMALIZE() function is to search for whole words exclusively. Although Analyzer offers word searches via the FIND() and LISTFIND() functions, these functions search for words within strings and not the solo words exclusively.
To search for whole words exclusively, one technique is to create a custom substitution table for use with the NORMALIZE() function. The structure of the substitution table would be:
WORD1 #####
WORD2 #####
WORD3 #####
WORD4 #####
The benefit of the substitution table is that the NORMALIZE() function ONLY replaces whole words, not words that are part of a larger string, so searching for the replacement value ensures that you are only locating occurrences of whole words.
When the NORMALIZE() function is applied as a computed field to a column of data, the computed field can then be searched for whole word replacement value (in the example above the replacement value would be “#####”. If the normalized computed field was called Search_Column, then the view filter would be:
FIND("#####",Search_Column)
This is an easy yet powerful way to search for a known list of problematic whole words as part of a travel expense or purchase card audit.
For more detailed information see NORMALIZE(), FIND(), and LISTFIND().