QuestionHow to calculate the number of words or specific characters in a string.
- How many words does the string "this is an example" contain?
- In the word Mississipi; how many times do the letters "ss" appear?
LEN([string]) - LEN(REPLACE( [string], ' ', ''))) + 1Replace [string] with the name of the string field, and make sure to use the right aggregation. If there is only one value of [string] as in the example workbook, ATTR should be used instead of SUM. Assuming no leading or trailing spaces, which can be removed with the TRIM() function, there should always be one more word in a string containing a phrase than there are spaces.
REPLACE( [string], ' ', '')
LEN([string]) - LEN(REPLACE( [string], ' ', ''))) + 1
Additionally, the above examples assume that the strings contain a series of words separated by single spaces. If your strings have multiple spaces (see example below), you will first need to use a regular expression to replace any number of spaces with a single space:
One two three
REGEXP_REPLACE([string], ‘ +’,’ ‘)
LEN([Col1 (Convert ss to |)]) - LEN(REPLACE([Col1 (Convert ss to |)],"|",""))