KNOWLEDGE BASE

Match Multiple Substrings using a Parameter


Published: 26 Jan 2017
Last Modified Date: 13 Feb 2017

Question

How to match multiple substrings using a parameter.

Answer

  1. Create a string parameter, and select "All" for Allowable Values
  2. Enter the substrings to be matched into the parameter textbox, separated by a specific character (for example, a space).
  3. Create a calculation like the following, where [string field] is the name of the field to compare to the parameter values, and [string parameter] is the name of the parameter:
  4. REGEXP_MATCH([string field],REPLACE([string parameter], " " , "|" ))

    The above calculation takes a list of values separated by spaces and replaces the spaces with |. Because | means OR in Regular Expression syntax, the calculation checks each value of [string field] against each substring of the parameter.

Additional Information

Limitations:

  • will only work with data sources that support REGEXP_MATCH(), or with an extract.
  • because the above method uses REGEXP_MATCH(), the parameter string is otherwise handled as a Regular Expression. Metacharacters (such as ^,+,*, and $) cannot be matched without a backslash (\^,\+,\*,\$), and metacharacter use without a preceding backslash may interfere with the proper functioning of the string match.
  • more advanced string parsing (removal of metacharacters, using a comma with a space as a separator, exact match, etc.) will require additional code
  • the values to match must be entered manually (checkboxes will not work), and 
  • the other limitations of parameters still apply

Note: the workbook linked below contains a similar implementation to the above, but it uses commas (without spaces) as the separator between substrings instead of spaces.
Did this article resolve the issue?