CountRegEx() returns the number of occurrences of the specified regular expression pattern in the input string text. If there is no match, 0 is returned.
This function performs regex operations that are case-sensitive. You can alternatively use the variant CountRegExI() to perform case-insensitive regex operations.
Syntax:
CountRegEx
(text, regex)
Return data type: numeric
Arguments
Argument
Description
text
The input string text within which you want to search for a regular expression.
regex
The regular expression pattern to search for.
Function examples
Example
Result
CountRegEx('abc123','[a-z]')
Returns 3.
CountRegEx('abc123','[a-z][0-9]')
Returns 1.
CountRegEx('abc123','[0-9]')
Returns 3.
CountRegEx('ABC','[a-z]{3}')
Returns 0.
CountRegExI('ABC','[a-z]{3}')
Returns 1. Because the CountRegExI() variant is used, text searches are not case-sensitive.
When to use it
Examples of use cases for CountRegEx() include:
Counting the number of times a particular text pattern occurs within a string. For example, you could search for instances of email addresses, telephone numbers, and other information within documents and email messages.
Validating whether data matches a specific syntax requirement. For example, you could identify occurrences of error in data entry.
Example 1 – load script to count valid year ranges
Overview
Open the Data load editor and add the load script below to a new section.
The load script contains:
A tabled named Vehicles containing a list of vehicles, along with the condition and model year of each vehicle.
For each record in Vehicles, a calculation to determine whether the year range for the vehicle meets the expected syntax. Valid year ranges include 1990s, 2000s, 2010s, and 2020s. This calculation is performed using the CountRegEx() function.
Example 3 – chart expression to count occurrences of string
Overview
Open the Data load editor and add the following load script to a new section.
The load script contains the text from a number of corporate correspondences that were sent out to employees of an organization. The data has already been cleansed of any personal identifiable information (PII), other than the company name and the email address that sent each correspondence. The body of each message has been collapsed into a single continuous string. In other words, all formatting and newline characters have been removed.
Our requirements:
Identify which messages contain a string that includes the email address TestCompanyNameCorrespondence@test.com, along with a specific introduction to this address that indicates that it was the sender.
We want flexibility in which introductions to identify, but also to still have some definite restrictions. The introductions Sender and Sent by are both considered valid. To achieve this goal, regex patterns can be used.
Case sensitivity of email addresses is not a factor. Therefore, the search should be case-insensitive.
Load script
Correspondences:
Load * Inline [
ID|MessageBody
1|Sent by-- TestCompanyNameCorrespondence@test.com Good morning TestCompanyName team! I hope you are all well. I am just reaching out about the fabulous benefits package we launched just year. Our goal is to take a poll of what you all think. Can you please rate the new options from 1-10 in a response to this email, by this Tuesday? Thank you!
2|Sender: TestCompanyNameIT@test.com Did you know you can access our IT portal anytime, from any of your company devices? It's true! Reach out to your team lead to learn more.
3|Sender: TESTCOMPANYNAMECORRESPONDENCE@test.COM URGENT: This is a message to inform employees of an ongoing issue with our local branch. The branch will be closed until further notice. Thank you
4|Sender: TestCompanyNameVolunteering@test.com Dear team! We are looking for volunteers to help with this year's charity event! We need 40 volunteers to help with the event. If you are interested, please send an email to your direct supervisor. Thanks, TestCompanyName team members, for helping make the world a better place!
5|Sender is TestCompanyNameCorrespondence@test.com Hi folks, it's time to announce the annual TestCompanyName staff party! The party will be taking place at the office this year. Please make sure to RSVP, and we hope to see you there! Sincerely, the TestCompanyName management team
] (delimiter is |);
Results
Load the data and open a sheet. Create a new table and add these fields as dimensions:
In the measure properties, set Totals function to Sum. This configures the totals row at the top of the table to count the total number of records that contribute to the count.
The measure counts the number of matches for the specified regex pattern within the message content. In the regex pattern, .* indicates any number of any character except newline characters. The pattern also accounts for variation in how the email address is introduced: both Sender and Sent by are valid matches. The CountRegExI() variant of the function ensures case-insensitive searches.
Sent by-- TestCompanyNameCorrespondence@test.com Good morning TestCompanyName team! I hope you are all well. I am just reaching out about the fabulous benefits package we launched just year. Our goal is to take a poll of what you all think. Can you please rate the new options from 1-10 in a response to this email, by this Tuesday? Thank you!
1
2
Sender: TestCompanyNameIT@test.com Did you know you can access our IT portal anytime, from any of your company devices? It's true! Reach out to your team lead to learn more.
0
3
Sender: TESTCOMPANYNAMECORRESPONDENCE@TEST.COM URGENT: This is a message to inform employees of an ongoing issue with our local branch. The branch will be closed until further notice. Thank you
1
4
Sender: TestCompanyNameVolunteering@test.com Dear team! We are looking for volunteers to help with this year's charity event! We need 40 volunteers to help with the event. If you are interested, please send an email to your direct supervisor. Thanks, TestCompanyName team members, for helping make the world a better place!
0
5
Sender is TestCompanyNameCorrespondence@test.com Hi folks, it's time to announce the annual TestCompanyName staff party! The party will be taking place at the office this year. Please make sure to RSVP, and we hope to see you there! Sincerely, the TestCompanyName management team
1
Across the five messages, there are three that match the specified regex pattern. Each message contains one match.
Information noteThe MatchRegEx() function is also useful for these types of validation use cases.
A measure is a calculation base on one ore more aggregations. For example, the sum of sales is a single aggregation, while the sum of sales divided by the count of customers is a measure based on two aggregations.