Query to count words SQLite 3

Question

Is there a way to count words in a text string?

I'm using SQLite 3 and I'm trying to write a query that takes a bunch of long strings of text, and counts the number of words in each one.

I also want to ignore html tags (or anything between carets) such as paragraph tags, break tags, etc.

So when I run a query selecting text from the appropriate column, I get a large wordy text output with some html tags in it, and I just want to count the words.

How can I write a query to do this?

Answer 1

As far as I know there is no way to directly count the number of words in a string in SQL lite 3. (I'm more familiar with mysql and ms sql)

You can use Length and Replace as a work around

 SELECT length(@String) - length(replace(@String, ' ', '')) + 1

Answer 2

The previous answer is incorrect for columns that are blank. You will need to add a case/when/then statement to your select:

SELECT someStr,
  CASE WHEN length(someStr) >= 1
  THEN
    (length(someStr) - length(replace(someStr), ' ', '')) + 1
  ELSE
    (length(someStr) - length(replace(someStr), ' ', ''))
  END as NumOfWords
FROM someTable;

Edited: If the column has 0 spaces, but had a word in it, it would incorrectly report 0. Changed the condition to allow for it.

Source: An Excel Trick I used to do the same thing

Answer 3

The answer from @Ziferius has a small syntax error, the following one is a working one, tested by myself.

SELECT someStr,   CASE WHEN length(someStr) >= 1   
THEN 
    (length(someStr) - length(replace(someStr, ' ', ''))) + 1   
ELSE 
    (length(someStr) - length(replace(someStr, ' ', '')))   
END as NumOfWords FROM someTable;