Use Regular Expressions (Regex) for Advanced Data Filtering and Manipulation

 

regex.png

How to Use Regular Expressions in Insycle

Regular Expressions, often referred to as regex, is a data searching and filtering language that can be used in your Insycle templates for advanced searching, filtering, and data manipulation. 

While regex knowledge is not required to use Insycle, it does allow for advanced, custom filtering that can give you data management superpowers. 

How Regex Works

A regular expression is a string that describes patterns that can be used to match and locate text within a field.

  • Regex reads the whole input and attempts to match the characters against the instructions, one character at a time.
  • Regex reads the characters in a field from left to right.
  • If a character matches the pattern, it returns true. If it does this, the regex can proceed to the next character in the string. If a character doesn’t match the pattern, it returns false. If this happens, it rejects the current character and starts matching again from the next one.

Using Regex in Insycle

Regular Expressions can be used in Insycle templates in multiple different ways. 

Regex in Record Filter

You can use regular expressions in the standard filters found in all Insycle modules and apps. Select a field, then select regex or negate regex from the Condition dropdown. 

Using regular expressions here, you can combine advanced regex filters with standard visual filters in Insycle.

This regex looks for zip codes that have less than five digits.

regex-3-zipcodelessthan5.png

This regex looks for any phone numbers that have letters in them, with an additional filter to remove records that may have an extension listed. 

step-1-filter-phone-regex-has-letters-no-ext.png

Regex in Functions

Regex can also be used within functions inside of the Transform Data, and Magical Import modules. Here, regular expressions can be used to creatively filter, copy, and manipulate data within fields.

The Transform Data module has four regex functions available:

  • Map: Regex – Use a regular expression (regex) to find a pattern in your data and replace any occurrence with a specified value.
    Example: If the field is Postal Code, with Existing Text parameter "1[0-4]{1}[0-9]{3}", and New Text "New York", then "10013" >> "New York"
  • Find & Replace: Regex any occurrence – Use regex to find a pattern in your data and replace any occurrence with a specified value.
    Example: If First Name field, with Existing Text "\(.*?\)", and New Text "(John)", then "Jonathan (Jack)" >> "Jonathan (John)"
  • ​​Extract: Regex copy – Use regex to find a pattern in your data and copy the value to another field while leaving the value in the original field.
    Example: If Parameter is "[0-9]{4}-[0-9]{2}", and the original value is "West Side Expo 2024-03-20", the value "2024-03" is copied to the Target Field.
  • Extract: Regex move – Use regex to find a pattern in your data and move the value to another field. The extracted value will be cleared from the original field while the rest of the value remains.
    Example: If Parameter is "[0-9]{4}-[0-9]{2}-[0-9]{2}", and the original value is "West Side Expo 2024-03-20", the value "2024-03-20" is moved to the Target Field, and the original value becomes "West Side Expo"

In this example from the Transform Data module, the regular expression looks at the last two sets of characters after the "." to get the country from the top-level domain.  

transform-data-contacts-extract-country-from-email-step-2.png

There are three regex functions available in the Magical Import module:

  • Map: Regex – Use a regular expression (regex) to find a pattern in your data and replace any occurrence with a specified value.
    Example: If the field is Postal Code, with Existing Text parameter "1[0-4]{1}[0-9]{3}", and New Text "New York", then "10013" >> "New York"
  • Find & Replace: Regex any occurrence – Use regex to find a pattern in your data and replace any occurrence with a specified value.
    Example: If First Name field, with Existing Text "\(.*?\)", and New Text "(John)", then "Jonathan (Jack)" >> "Jonathan (John)"
  • Extract: Regex first occurrence – Use regex to find a pattern in your data and use the first occurrence of the value.
    Example: If Parameter is "[0-9]{4}-[0-9]{2}", and the original value is "West Side Expo 2024-03-20", the value "2024-03" is kept.

In this example from the Magical Import module, the regular expression is looking for any lowercase letters in the Phone Number values.

magical-import-contacts-format-phone-step-3-regex-replace-format-E-164.png

Regex in Advanced Search

Regex can also be used in any Insycle module that offers the ability to filter data in the Advanced tab.

regex-1.png

You could start building your filter using the Filter tab, then click the Advanced tab and customize your regex string further. Any filter you build using the Filter UI is represented by a regular expression string. 

The Advanced Search syntax works as follows:

+Field:Regex

A negate regex has a minus (-) in front of the field instead of a plus, like:

-Field:Regex

Building Regular Expressions

You can use regular expressions to:

  • Validate that a field (or a portion of the value) matches a pattern
  • Find fragments of text that match a pattern in a field
  • Extract fragments of text from a field

To use regex, select the regex function when filtering data:

regex-filter-postal-code-california.png

Here are some examples of how regular expressions work:

Regex Matches any specified field that

hello

contains {hello}

gray|grey

contains {gray, grey}

gr(a|e)y

contains {gray, grey}

gr[ae]y

contains {gray, grey}

b[aeiou]bble

contains {babble, bebble, bibble, bobble, bubble}

[b-chm-pP]at|ot

contains {bat, cat, hat, mat, nat, oat, pat, Pat, ot}

colou?r

contains {color, colour}

rege(x(es)?|xps?)

contains {regex, regexes, regexp, regexps}

go*gle

contains {ggle, gogle, google, gooogle, goooogle, ...}

go+gle

contains {gogle, google, gooogle, goooogle, ...}

g(oog)+le

contains {google, googoogle, googoogoogle, googoogoogoogle, ...}

z{3}

contains {zzz}

z{3,6}

contains {zzz, zzzz, zzzzz, zzzzzz}

z{3,}

contains {zzz, zzzz, zzzzz, ...}

[Hh]e\*\* o

contains {He**o, he**o}

[0-9]

contains {0,1,2,3,4,5,6,7,8,9}

[2-9]|[12]\d|3[0-6]

contains an integer in the range 2..36 inclusive

mi.....ft

contains a nine-character (sub)string beginning with mi and ending with ft

[^w*&3@]

contains any character other than a w, asterisk, ampersand, 3, or at-sign

Examples

Company Name: Not Capitalized

This regex filter looks for company names that have no capitalized letters.

regex-1-company-name.png

Non-Standard Deal Names

This filter looks for deals that do not follow the naming convention: any text followed by / followed by any text followed by / followed by any text followed by / followed by any text followed by /

transform-data-deals-standardize-deal-name-step-1-negate-regex.png

Invalid Zip Code: Less than 5 Digits

This regex filter looks for zip codes that have less than five digits.

regex-3-zipcodelessthan5.png

Invalid Phone Number: More than 16 Digits, or Less Than 8

This regular expression in the Advanced Search looks for invalid phone numbers with more than sixteen or less than eight digits.

regex-4-phonemorethan16lessthan8.png

Invalid Phone Number: Repeating Digits

This regex filter tells Insycle to look at the Phone Number field and see if any digit repeats itself more than five times. This is a sign of an invalid phone number. 

regex-5-phonerepeatingdigits.png

First Name: Just 1 Letter (Initials)

This regex looks for entries in the First Name field that have just one letter in the field.

regex-6-firstnamejust1letter.png

Job Title: Find Records with Titles for C(X)O Executives

This filter looks in the Job Title field for records that follow the "C_O" convention, filtering for c-suite records.

regex-7-jobtitleCXO.png

Zip Code: In California

This regex filters records down to those that have a zip code in California. All California zip codes begin with nine, then a number between zero and six, then a number between zero and one, then two numbers between zero and nine at the end.

regex-8-ZipCodeinCalifornia.png

Find IDs Matching a Pattern

This filter looks for ID numbers that start with 4 letters, followed by 3 numbers, and then 6 more letters. 

regex-9-ID-4Letters-3Numbers-6Letters.png

Invalid Phone Number: Includes Letters (But Not an Extension)

This regex filter looks for any phone numbers that have letters in them, with an additional filter to remove records that may have an extension listed. 

regex-10-PhoneNumberWithLetters.png

Additional Resources

Related Help Articles

Related Blog Posts