Counting words in a sqlite FTS4

Question

I have a sqlite3 full text search table defined like this:

 CREATE VIRTUAL TABLE entries USING fts4 ( entry TEXT )

Each entry row has a line of text. How can I write a query to count the total amount of words in the table? Thanks

Answer 1

I don't know of a built-in function to do that, but you could re-use the answer to " Query to count words SQLite 3 " to get the total count of words:

select sum(length(trim(entry)) 
        - length(replace(trim(entry), ' ', '')) + 1) from entries;

(Modified the original answer by adding the trim.)

If you have sqlite3 version 3.7.6 or later, you can do something cleaner with an fts4aux table.

create virtual table terms using fts4aux(entries);
select count(distinct term) from terms;