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

A regular expression is a sequence of characters that defines a search pattern. Here's how regex works:

1. Regex reads the entire input, attempting to match characters against the defined pattern.
2. It processes characters from left to right.
3. If a character matches the pattern, it returns true and moves to the next character.
4. If a character doesn't match, it returns false, rejects the current character, and starts matching from the next one.

Let's look at a simple example:

Pattern: a.c
The "." in the pattern represents any single character.

This pattern will match: "ABC," "a1c," or "a@c" but not "abbc," or "ac."

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. 

Examples

Finding Zip Codes with Less Than Five Digits

• Field: Postal Code
• Condition: regex
• Value: [0-9]{1,4}

This regex pattern, "[0-9]{1,4}", matches any sequence of 1 to 4 digits, effectively finding zip codes with less than five digits.

Identifying Phone Numbers with Letters (Excluding Extensions)

You can combine advanced regex filters with standard visual filters in Insycle.

This combination uses two filters:

Filter 1: This regex, ".[a-z]." finds any phone number containing lowercase letters.

• Field: Phone Number
• Condition: regex
• Value: .*[a-z].*

Filter 2: This filter excludes entries likely to be extensions.

• Field: Phone Number
• Condition: doesn't contain
• Value: x|ex|ext

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

The Transform Data module offers four regex-based functions:

Map: Regex

Find a pattern in your field data and replace it with a specified value

Example: Convert specific zip codes to city names

• Field: Postal Code
• Existing Text: 1[0-4]{1}[0-9]{3}
• New Text: New York

Result: "10013" becomes "New York"

Find & Replace: Regex any occurrence

Find a pattern in your data and replace any occurrence with a specified value

Example: Replace nickname in parentheses

• Field: First Name
• Existing Text: \(.*?\)
• New Text: (John)

Result: "Jonathan (Jack)" becomes "Jonathan (John)"

Extract: Regex copy

Find a pattern in your data and copy the value to another field while leaving the value in the original field

Example: Extract country code from email address top-level domain (TLD)

• Field: Email
• Parameter: (?:\.[^.]{2,2})?$
• Target Field: Country/Region

This regex will match and extract two-letter country codes at the end of an email address (e.g., .uk, .de, .fr) but nothing if the email ends with a longer TLD (e.g., .com, .org, .edu)

Result: For the email value "john@example.co.uk," copies ".uk" to the Country/Region field

Extract: Regex move

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: Identify and remove any alphabetic characters from phone numbers, moving them to a separate field

• Field: Phone Number
• Parameter: [a-zA-Z]{1,}.*
• Target Field: Extension

This regex looks for any sequence of alphabetic characters in the phone number field. If it finds alphabetic characters, it will move the matched part (and any characters that follow) to the target field and remove the matched part from the original Phone Number field.

Result: For the phone number "401-555-1212 x123," moves "x123" to the Extension field

Example

The example below demonstrates how functions are being applied sequentially to transform the email field data into a standardized country or region name. Country information is extracted from email addresses and then standardized to a consistent format.

1. Extract: Regex copy - This function uses a regular expression "(?:.[^.]{2,2})?$" to extract a portion of the email address. This regex will match and extract two-letter country codes at the end of an email address (e.g., .uk, .de, .fr) but nothing if the email ends with a longer TLD (e.g., .com, .org, .edu)
2. Remove: Symbols - This function removes symbols from the extracted text
3. Map: Values - This function maps the value "uk" to "gb" to convert the United Kingdom country code from "uk" to the ISO standard "gb"
4. Standardize: Country Code2 to Name - This function converts two-letter country codes to full country names

Regex can also be used within functions in the Magical Import module. Here, regular expressions can be used to creatively filter, copy, and manipulate data within fields.

The Magical Import module provides three regex functions:

• Map: Regex – Same as in the Transform Data module. See above
• Find & Replace: Regex any occurrence – Same as in the Transform Data module. See above

Extract: Regex first occurrence

Find a pattern in your data and use the first occurrence of the value

Example: Import only the city from a combined city, state field

• CSV Column: City & State
• Function: Extract: Regex first occurrence
• Parameter: \w+

and

• Function: Copy: Value
• Target Field: City

Result: The CSV column City & State value "Minneapolis, MN" is imported to the City field as just "Minneapolis"

Example

The example below demonstrates how functions are being applied sequentially to remove extra characters from phone numbers and standardize them. Two operations are being applied to the Phone Number values:

1. Find & Replace: Regex any occurrence - This function uses the regular expression [a-z]{1,}.* to find and remove (New Text = blank) any sequence that starts with one or more lowercase letters, followed by any number of characters (including none)
   For example, this will match and remove:
   • "ext 123" in "123-456-7890 ext 123"
   • "home:" in "home: 987-654-3210"
2. Format: Phone E.164 Standard +xxxxxxxxxx - This formats the cleaned-up numbers to the E.164 international standard

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

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

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:

Here are some examples of how regular expressions work:

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

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 /

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

This regular expression in the Advanced Search looks for invalid phone numbers with more than sixteen or less than eight 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. 

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

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

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.

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

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.