Expr Pattern Matching

Expr language allows you to check text values against a certain pattern, when using the MATCH, CASE, REPLACE or SEARCH function. 

There are three types of patterns that can be used: Exact Matching, Wildcard Matching, and Regular Expression Matching.

Exact Matching

This is the simplest pattern type, which compares value against an exact text value:

• MATCH(value, "Apples")

Although it's called "exact matching", there are some additional rules that make the matching easier:

• All leading and trailing whitespace characters are removed from the value.

• Text comparison is case-insensitive, which means APPLES will match Apples.

• The value (without leading and trailing spaces) must match the whole pattern.

Exact matching is used by default, unless the pattern is recognized as requiring Wildcard or Regular Expression matching.

Wildcard Matching

Wildcard patterns let you use the wildcard symbol "*" to specify any number of any characters (including no characters).

• MATCH(value, "App*")

The above function would return "1" for any value that started with the characters "App" – so "App", "Apple" and "Apples are good for you" would all match. You can also use multiple asterisks to build your pattern. Match(value, "A*L*") would match anything that starts with an A and contains an L, including "Apples", "Almanac" and "Aunt Sal".

Wildcard matching uses the same rules as exact matching:

• All leading and trailing whitespace characters are removed from the value.

• Text comparison is case-insensitive, which means APPLES will match App*.

• The value (without leading and trailing spaces) must match the whole pattern.

Wildcard matching is used when the pattern is not recognized to be a Regular Expression Pattern but contains at least one asterisk.

Regular Expression Matching

This type of matching lets you use powerful regular expressions to specify exactly what you need to match with.

• MATCH(value, "/^Ap+.*s$/")

Structure uses regular expressions available with Java. For full documentation about the regular expression language, see Java documentation for Pattern.

The regular expression matching is different from other types of matching. The following rules apply:

• Leading and trailing whitespace characters are not removed.

• Text comparison is case-insensitive, like with the other types of matching.

• The value does not have to fully match the pattern – it is sufficient that at least one occurrence of the pattern is found in the value. To make your pattern match the whole text, use "^" and "$" characters in the pattern.

Regular expression matching is turned on if the first and the last characters of the pattern are "/". (These characters are removed, as they are not a part of the pattern.)