Documentation Visual SQL FAQs

Count keywords and characters in strings in Visual SQL

If you joined before January 6, 2020, you might be using our old Data Explorer interface. Check out Count keywords and characters in strings in Data Explorer for more relevant content.

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 Formula 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.

Use a Custom formula to mark the rows containing your keyword

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

Use a Custom formula to flag rows containing your keyword

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 Formula Column and select Custom for the formula type. You’ll then enter the following formula, replacing Column Name with your column name:

length("Column Name")

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

Use length() to get the total number of characters in a string

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",' ','')))

Count all characters excluding spaces using length() and replace()