Regular expressions in Jitterbit App Builder

Overview

A Regular Expression is a notation, or specifically, a sequence of symbols and characters expressing a string or pattern. They are generally used in the Business SQL area of App Builder to validate user-input or narrow down/find a specific pattern within a large piece of text.

Any time LIKE is used in a where clause with a wildcard (%), you're using Regular Expressions. You may be familiar with the statement WHERE MyField LIKE 'ABCD%', but you can also use brackets to search for a range WHERE MyField LIKE '[A-B][C-D][A-Z][A-Z]%' which will return any record with a combination of A or B as the first character, C or D as the second, any alpha characters in the next two characters, and anything else at the end.

The same Reg Ex structure applies to numbers. If you're filtering on a range of values, say 900-919, you can filter WHERE MyField IN ('900', '901', '902'... etc) but it's cleaner to use WHERE MyField LIKE '9[0-1][0-9]' (dealing with string fields here, not numerics). you can find phone numbers that fit the pattern WHERE MyField LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'

Below is an example similar to the example above where, in this case, the expression is validating whether records in FieldValue contain the character strings 900S, 901S, 902S, ..., 919S.

You can also use the ^ character which represents NOT in the string WHERE MyField LIKE '[^0-9]%' which will return records with a non-numeric as first character, followed by anything. WHERE MyField LIKE '[A-Z][^A-Z]' will return records with two characters, the first being alphabetic and the second being non-alphabetic.

There are a TON of other powerful tools within Regular Expressions that can be used with App Builder - the ones mentioned in this article are a few of the common ones used. Here is a tutorial link if you want to learn more: https://www.mssqltips.com/sqlservertutorial/9108/precise-alphabetic-filtering-with-regular-expressions-for-tsql/