REGEXFIND()

The REGEXFIND() function allows you to construct a regular expression to find matching patterns in data. Regular expressions are powerful and flexible search strings that combine literal characters and meta-characters, which are special characters that perform a wide variety of search operations. See Regular Expressions for specific details on the full list of special meta-characters.

The REGEXFIND() function returns a logical value indicating whether the pattern specified by a regular expression occurs in a string.

Note: The current implementation of regular expressions in Analyzer does not fully support searching languages other than English.

Constructing regular expressions can be tricky, especially if you are new to using regular expression syntax. You may be able to achieve your search goals using simpler search functions such as FIND(), LISTFIND(), MATCH(), or MAP(). If your search requirements exceed the capabilities of these simpler functions, regular expressions provide almost unlimited flexibility in constructing search strings.

If you want to replace matching patterns with a new string using regular expressions, see REGEXREPLACE() function.

Function Syntax

REGEXFIND(string, pattern)

Note: The search is case-sensitive, which means that uppercase and lowercase alpha characters must be explicitly specified.

Examples

The first simple example uses the group, alternation, and quantifier meta-characters to create a regular expression that finds “Smith”, “Smyth”, “Smithe”, or “Smythe” in the Last_Name field.

REGEXFIND(Last_Name,"Sm(i|y)the{0,1}")

Matching between the string and pattern parameters is performed sequentially. In the example above, “S” is matched against the first position in the Last_Name field, “m” is matched against the second position, “i” and “y” are matched against the third position, “t” is matched against the fourth position, and so on.

Spaces (blanks) are treated as characters in both the string and pattern parameters, so you should exercise care when dealing with spaces. In the pattern parameter, you can indicate a space either literally, by typing a space, or by using the meta-character \s. Using the meta-character makes spaces easier to read, and less likely to be overlooked, in a regular expression.

The second example demonstrates concatenating two or more fields in the string parameter when you want to search across multiple fields simultaneously.

REGEXFIND(Vendor_Name+Vendor_Street,"Hardware.*Main")

searches both the Vendor_Name and the Vendor_Street fields for the words “Hardware” and “Main” separated by zero or more characters. A business with the word “Hardware” in its name, located on a street called “Main”, would match the regular expression. So would a business called “Hardware on Main”. The concatenated fields are treated like a single field that includes leading and trailing spaces from the individual fields, unless you use the ALLTRIM() function to remove leading and/or trailing spaces.

Because REGEXFIND() searches for the characters in the pattern parameter in the order in which you specify them, the order in which you concatenate the fields has an effect. If you reversed Vendor_Name and Vendor_Street in the expression above, you would be less likely to get any results.

The following table lists some additional examples of using the REGEXFIND() function: