The problem occurs if you have a list of values, that is values separated with line break, and want to compare a string to see if it exist inside the list. This is very common when working with the List() function, getNthRecord(), ValueListItems(), iterating records or writing recursive custom functions.
Typically you will use PatternCount( listOfValues ; findString ) to find out if the value exist inside the list, but that is not a safe method. What you need to to to make it safe is to add line breaks as padding on both parameters.
PatternCount( "¶" & listOfValues & "¶" ; "¶" & findString & "¶" )
There reason for padding the findString is to make sure that it does not match a substring (part of a value). It must begin and end with a line break to be considered a match.
Padding on listOfValues is because if the match we are looking for is at the very beginning or end of the list there is no starting or ending line break unless we add them ourself.
Example list #1
- Orange
- Banana
- Applepie
- Ice Cream
If in want to find out if the value "Apple" is a member of the list above, the calculation PatternCount( listOfValues ; "Apple" ) will return true because it finds "Apple" in "Applepie" even though the value "Apple" is not a member of the list. We know that all values are surrounded by line breaks so by simply adding line breaks around the search string no match will be found on "Applepie"
Example list #2
- Java
- Javascript
- PHP
- Python
If we want to find out if the value "Java" is a member of the list above we will use the same method as before. We add line breaks before and after the search string. This however still returns false, no match is found because there is no initial line break before the value "Java". Therefore we must use line break padding on the list of values as well.
This is a simple but effective way to make safe matching of strings in a list of values. There might be other methods to do this in another way, choose which every suits you, but please make sure not to simply look for a unpadded string in a list of values. It will return unexpected errors!
Hope this helps someone
This site doesn't seem to be updated anymore. But for anyone stumbling upon this solution when googling (as I did), here's a comment:
ReplyDeleteThis technique might fail when trying to find the number of occurrences in the value list. If two list items next to eachother both are supposed to match they won't both be matched since the patterncount is not inclusive.
Let's say you have the list:
¶
Apple¶
Apple¶
Orange¶
Pear¶
When searching for "¶Apple¶" only one will be found. Since that match "uses up" both of the carriage returns around it, there is one too few to match the next one.
This is something to keep in mind. In my solution I know that I won't match any substrings of an item, so I can do without the carriage return padding.