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 strings in the Visual SQL Pipeline.
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 “Name” column containing the word
Inc. 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 Calculated Column to the Pipeline, select Custom for the formula type, and enter the CASE statement below, replacing
Name with your column name and
Inc with your keyword:
case when "Name" like '%Inc%' then 1 else 0 end
Note: Make sure to wrap your keyword in wildcards (%) to make sure 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 “Name” column— then use a Group & Aggregate Action 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
Inc is present in the “Name” 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:
case when "Name" like '%Inc%' then 'yes' else 'no' end
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 Action 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 Calculated Column 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:
length((replace("Column Name",' ','')))