Counting the occurrence of a specific letter within a string (QGIS)?

Question

I have a field which contains a series of words separated by commas.

I am wondering if it is possible to count the number of times the letter 'G' occurs. Alternatively it might be possible to split the field by commas and go down another avenue...

Here's a few example fields:

• Urban,Green,Urban,Green,Green
• Urban,Urban,Urban,Urban,Green,Urban
• Urban,Green,Green

Essentially I would like to produce a new column which tells me how many times 'Green' appears in each field.

Answer 1

array_length(
    array_filter(
        string_to_array("FIELD_NAME"),
        @element='Green'
    )
)

Change FIELD_NAME into your field name. If string is separated by another character, you should add that character to string_to_array function as seperator. For example:

string_to_array("FIELD_NAME", ';')

For Urban,Green,Urban,Green,Green string:

• string_to_array("FIELD_NAME") returns ['Urban', 'Green', 'Urban', 'Green', 'Green']
• array_filter(ARRAY, @element='Green') returns ['Green', 'Green', 'Green']
• array_length returns 3

Answer 2

Here's a way to find the number of Gs. Replace everything that isn't a G with an empty string, and count the length of what is left:

length(regexp_replace('Urban,Green,Urban,Green,Green','[^G]',''))
-> 3

length(regexp_replace('Urban','[^G]',''))
-> 0

[^G] is a regexp pattern that matches any character that isn't a G.

Answer 3

It might be simpler ways of doing it, but I would have done it by a custom function in the field calculator.

Open the attribute table for your layer. Open the field calculator (Ctrl-I or abacus icon)

Go to edit function. In the upper edit field, delete what is there and write

 from qgis.core import *
 from qgis.gui import *

 @qgsfunction(args='auto', group='Custom')
 def cnt(field, substring, feature, parent):
     return field.count(substring)

Press Save and load functions,

Go back to the expression editor and write

cnt(<fieldname>,<searchstring>)

Define a fieldname and press Done.

Answer 4

This expression should work:

array_length( 
    array_remove_all(
        array_foreach( 
        string_to_array( "column_name" ),

            array_find( string_to_array( @element ), 'Urban' )

        ),
        -1
    )
)

It turns turns a comma separeted sequence of strings, for example Urban,Green,Urban,Green,Green into an array.

Then it checks each element if it contains the string 'Urban', outputing an array where -1 means the element is not the string 'Urban'.

Then it creates a new array without the -1 elements, and finally counts the length of that array to get the instances of the search string.

Just replace the string 'Urban' with whatever you want to count.