on 01-27-2015 09:50 PM - edited on 07-27-2021 11:46 PM by APIUserOpsDM
RegEx….it can be tough, but extremely useful when looking for ways of extracting information from a string. Regular Expressions are basically a code you can write to match a specific set of characters (it's a pattern matching syntax). It could be something as simple as finding the three-digit number in the string “Eample123Eample” or something more complicated like using hexadecimal to select a certain range of characters. RegEx is something that can be used in Alteryx via the RegEx tool in the Parse toolset and some of the main uses for RegEx are replacing, matching, and parsing specific characters within a string.
Replacing text is a very handy process when working with string fields, as it can be used to replace virtually any set of characters. In the attached example module you will find the following data in Text Input Tool:
What I would like to do for this example is replace the portion of the string “file” and only that portion. The easiest way to do this using the RegEx tool is to follow these steps:
In this example, I choose “FileNumber_”. Once a Browse tool is connected and the module is run, you can see that the output has replaced all of the character matching “file” with “FileNumber_”.
Using RegEx to match is another process that can be very helpful for your data preparation or transformation process. Using the Match output method will assign a True or False value for you data based on the RegEx that you have written. I’m going to use the file initial data that was used above, but this time I was to match only the records that are “file1…”, “file2…”, and “file3…”. To do this, I have written a regular expression of: file[123].+ . The breakdown of the RegEx script is as follows:
As you can see in the below output, only records that had a file name of file1, file2, or file3 have been returned as a True match, while file4 returns False:
Parsing with the RegEx tool can be a little bit more complicated, but again it is an extremely useful process that will help in your data prep and transformation stages. The nice thing about parsing with the RegEx tool is that it also allows you to alter the fields upon output. Once you have a valid regular expression for parsing, you will be able to change the field name, the data type, and the size of the field. The trick to parsing using RegEx is that you have to create a group, and this is done by using parenthesis “()” to identify each group. You can use multiple groups parse into multiple field, but for this example, there will just be one group.
Using the same initial file name field, we will use a regular expression to parse just the file name without an extension. For this, I have used the following expression: ^(.+)\..+$ . Just like before, the breakdown is as follows:
Here is the outcome of using the parse method to find just the file name:
I hope this helps you get started with regular expressions! As always, if you have any questions please don't hesitate to email support@alteryx.com. Special thanks to Mike Akey for putting together this post, until next time!
Hope This Example will used in most of the cases.
Thanks for putting this together! This is a great place to start using RegEx in Alteryx Designer.
I'm gonna leave these here as a sort of "Would you like to know more" 😁