Introduction
Regular Expressions (shortened as REGEX or REGEXP) is a sequence of characters that specifies a match pattern in a text. Usually, such patterns are used by string-searching algorithms for ‘find’ or ‘find and replace’ operations on strings or for input validation.
A basic example of regular expression would be regex /c*t/ which would match “cat”, “cot”, or “cut”, but not “pat” or “but”.
Tableau offers four REGEX functions which include.
REGEXP_EXTRACT (string, pattern) – which extracts a text from a string field that matches the specified REGEX pattern.
REGEXP_EXTRACT_NTH (string, pattern, index) – which is executed same as above, only that it extracts the nth matching group specified by the index.
REGEXP_MATCH (string, pattern) – which returns TRUE if pattern is matched in the string field.
REGEXP_REPLACE (string, pattern, replacement) – Which replaces the matched pattern with the specified replacement text.
In this short article, I will be sharing three use cases of regular expressions in Tableau.
Case 1: Extracting email addresses from a string of text.
The regular expression pattern for email addresses is,
[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}
To demonstrate this, I have connected the following text data which contains customer email addresses - the goal here is to demonstrate how you can use REGEX functionality to extract the email addresses.
To extract the email addresses from this text data, I will create the following calculation.
Note: The pattern in this calculation is enclosed in parentheses (capture group) - so Tableau can treat the multiple characters as a single unit.
Adding the calculation to the view.
(Which extracts each customer’s email address).
Case 2: Extracting a URL
The regular expression pattern for extracting URLs is,
(http|https)://([\w_-]+(?:(?:\.[\w_-]+)+))([\w.,@?^=%&:/~+#-]*[\w@?^=%&/~+#-])?
Just like in the first case, you can extract URLs from text data using the following REGEX calculation.
Note: The pattern in this calculation is enclosed in parentheses (capture group) - so Tableau can treat the multiple characters as a single unit.
Executing this calculation extracts the URLs from the text data.
Case 3: Extracting numbers from a string of text
Using Regular expressions, you can extract numbers from a string of text using the following calculation.
Pattern: [0-9]+
Note: The pattern in this calculation is enclosed in parentheses (capture group) - so Tableau can treat the multiple characters as a single unit.
Executing this calculation extracts the numbers from the text.
Conclusion
I hope this article was helpful to you. If you wish to receive more Tableau tips and tricks, kindly join our mailing list by subscribing below.
If you like the work we do and would like to work with us, drop us an email on our contacts page and we’ll reach out!
Thank you for reading!
