This blog will cover 1) why use Regular Expressions? and 2) how to use Regular Expressions?
1) Why use Reg Ex?
Say you had data about sofa listings online. You had the sellers name, product name, and the description that the seller left.
It would be very helpful to grab the price of the items and make it into its own column.
We can do this using Reg Ex!
For this blog's explanation, I like to use this definition from Loyola Marymount University : "A regular expression is a pattern that matches strings or pieces of strings".
Essentially, what we want to accomplish with our Reg Ex is to find a piece of string within a string - like the dollar amount inside the description.
Lets see how we could do that:
2) How to use Reg Ex.
Before we dive into a data manipulation tool, let's get a feel for how regular expressions work. Navigate to regex101.com to follow along:
Also open this Mozilla's guide for Reg Ex syntaxes, but today we are focusing on '\d' and '+'.
The basic idea of Reg Ex is to use the characters above to capture a part of the string that you want. For example, take the string "This sofa is $14".
In regex101.com, if you type in "14" into the regular expression, and "This sofa is $14" into the test string it will highlight any mention of 14 in the test string.
If the test string is changed to "I have 14 sofas for $14 each", the outcome will be like this.
What happens is that the test string is scanned for any match to the regular expression. Currently, it finds any occurrence of 14 in the test string and finds them.
But what if you want to find occurrences of any numbers in general?
Then we can start to use the Reg Ex character classes and quantifiers.
If you use '\d', you will see that it highlights any number.
What you may notice is that the highlight color is different for the 1 and 4 in 14. This is because we are scanning for any digit of length one.
If we want to look for any digits of length two, you can repeat '\d' again and it will find all substring with exactly 2 digits.
What if we want any numbers - meaning numbers with any number of digits?
Then we can use quantifiers like '+' after the '\d'.
This will capture any piece of substring made up of 1 or more digits.
You can hopefully begin to see how powerful and expansive regular expressions become when combining different character classes and quantifiers.
I will end the blog just by showing how you can use regular expressions in Alteryx!
Here is the same dataset you saw in the beginning, now in Alteryx.
If we want to extract the price of the sofa from the description, we can drag in the Reg Ex tool under the Parse tab.
If we use the regular expression '\d+' to look through the [Listing Description] column, we can find the any number listed in the description.
The configuration above will look for any number in the description and put it into a new column.
And here's the output!
This was a very simple overview of how you can use Reg Ex in Alteryx.
Obviously, the current expression can be very problematic (for instance, if the description was "2 sofas for $100", the current flow will extract 2 instead of 100). The current expression works because all use of digits in the description is for the price, but depending on what your dataset looks like your Reg Ex will change.
Hopefully, this set up a foundation for how regular expressions can be helpful when manipulating data, and also gave you an idea of where to get started in Alteryx!