Query To Count Words Sqlite 3

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 eac

Solution 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

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

Solution 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,
  CASEWHEN length(someStr) >=1THEN
    (length(someStr) - length(replace(someStr), ' ', '')) +1ELSE
    (length(someStr) - length(replace(someStr), ' ', ''))
  ENDas 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

Solution 3:

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

SELECT someStr,   CASEWHEN length(someStr) >=1THEN 
    (length(someStr) - length(replace(someStr, ' ', ''))) +1ELSE 
    (length(someStr) - length(replace(someStr, ' ', '')))   
ENDas NumOfWords FROM someTable;