Count keywords and characters in strings

Summary

Sometimes it’s useful to get the number of times a word or character occurs within a long string—maybe to use as part of a formula or part of conditional formatting. Here are a few ways to count parts of a string in a Visual SQL step.

Solution

Count keywords in strings

You can easily count the number of instances a specific word is found in one of your columns.

For example, we’d like to count the number of rows in our “Summary” column from the “Jira Issue” table containing the word test. We want to count any result containing this word in a row without excluding results that are not exact. To do this, we’ll use conditional logic with a CASE statement.

We need to add a "Formula column" step, select Custom for the formula type, and enter the CASE statement below, replacingSummary with your column name and test with your keyword:

Note: Make sure to wrap your keyword in wildcards (%) so the format accounts for strings with your keyword at any place in the string.

To get the count of how many rows contain your keyword, hide the original column—for our example, we’d hide the “Summary” column— then use a "Group & aggregate" step to find the Total sum of your custom column. You might think to use “Count” as the aggregation, but that would return the count of all rows in your result set.

Alternatively, you could create a flag where every time the word test is present in the “Summary” column, the word yes would appear. If this keyword isn’t present, the word no would appear. This can also be done using a CASE statement in a custom formula:

To get a count of the rows flagged with yes, you can filter your result set to only include rows where your custom column has a value of yes. Then, you can use a “Group & aggregate” step to get a Count of the remaining rows. “Total sum” wouldn’t work in this case since the values are strings, not numbers.

Count all characters in strings

You can also count all the characters within a string by using the LENGTH function. This information may be useful to use as part of other formulas.

To do this, simply add a “Formula column” step and select Custom for the formula type. You’ll then enter the following formula, replacing Column name with your column name:

Note: Using this formula counts all characters in the string, including spaces.

If you’d like to exclude spaces from the character count, you can nest the REPLACE function within the LENGTH function to remove any spaces from your string before you get the character count. You’d use the following formula, replacing Column name with the name of your column: