12

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?

Mat
  • 202,337
  • 40
  • 393
  • 406
user396247
  • 123
  • 1
  • 5
  • Very related: [string - How to count instances of character in SQL Column - Stack Overflow](https://stackoverflow.com/questions/1860457/how-to-count-instances-of-character-in-sql-column) -- (just count the number of spaces and add one, as long as there's no double space/space at the start or end of the string) – user202729 Aug 13 '21 at 08:14

3 Answers3

23

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
b8b8j
  • 584
  • 1
  • 5
  • 13
1

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

Ziferius
  • 91
  • 1
  • 6
0

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;
N1kt0
  • 1
  • 2