Creator: Using Regular Expressions in a question

Regular Expressions let you search for particular patterns in a text string, and can be a powerful ally on your data analytics journey.

Sometimes when we're analysing data we need to pull specific patterns out of a string. For example, consider the following list of descriptive grades:

• A+ (98%)
• A (89%)
• A (87%)
• B (75%)
• C+ (68%)
• D- (51%)

We want to create a chart that counts the number of grades that have been awarded. But if we try to group on the list of grades, we'll get two counts for "A" because of the different percentages appended to the descriptive grade.

The solution to this is using a regular expression.

➡️ Understanding regular expressions

A regular expression is a sequence of characters that specify a search pattern. There is a specific syntax to building a regular expression. Albitros uses JavaScript-compatible regular expression syntax.

If you're new to regular expressions, Regexr is a great tool for understanding how to build them.

([A-E|U](G|\+|-)?)     --becomes-->   ([A-E|U](G|\\+|-)?)

➡️ Creating a custom field with a regular expression

Assuming you have already created a question with the data you wish to work with, in the Query Editor:

1. Click on the Custom column button. The formula bar should appear:
   
2. In the field formula, select the regexextract function:
   
3. In the text variable of the function, enter the field you want to work with, and put the regular expression in the regular_expression variable position (surrounded by double quotes, and ensuring you escape any back-slashes):
   
   
   
4. Enter a name for your custom column, and click Done.
   Your custom regular expression column should now be visible in your question results: