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

 

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. 

1. Regex reads the whole input and attempts to match the characters against the instructions, one character at a time.
2. Regex reads the characters in a field from left to right.
3. 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, reject the current character and start matching again from the next one. Start from II)

How To Use Regex in Insycle

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

1. Regex in Standard Filter

You can also use regular expressions in a standard Insycle filters and templates. Select Regex from the condition dropdown after selecting your field. 

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

2. Regex in Functions

Regex can also be used within functions inside of modules like Transform Data, and Magical Import. 

Here, regular expressions can be used to creatively filter, copy, and manipulate data within fields.

In Transform Data, there are three functions regex functions:

• Regex find and replace any occurrence. Use regex to identify data within a field, then replace it.
• Extract regex copy. Use regex to copy a portion of a field. 
• Extract regex move. Use regex to copy and move a portion of a field into a new field. 

3. Regex Filtering in Advanced Search

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

Here, any filter you build using Insycle's UI is represented by a regular expression string. 

You can build your filters from either tab. If you prefer regular expressions, you can build custom regex filters. You can also build your filter using the visual UI, then click the Advanced tab and customize your regex string further. 

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 (regex) to:

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

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

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 an w, asterisk, ampersand, 3, or at-sign.
^john	begins with "john"
john$	ends with "john"
^john$	is exactly "john"

 

Examples

Company Name: Not Capitalized

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

First Name: Not Capitalized

This regex looks for first names that have no capitalized letters.

Invalid Zip Code: Less than 5 Digits

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

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

This looks for invalid phone numbers with more than sixteen or less than eight digits.

Invalid Phone Number: Repeating Digits

 This regex 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. 

First Name: Just 1 Letter (Initials)

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

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

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

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.

Filter IDs

This is an example filter for an ID number. It looks for IDS that start with 4 numbers, followed by three letters, and then six more numbers. 

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

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.