What would be the best way to get a total word count for all posts on the forum? I'd also like this to include Blog posts and comments, Status Updates, and Clubs, but not anything in the personal messenger system.

Thank you!

There's no clean way to achieve this because the content columns contain the content as HTML code, so one can't just count the words, so first you'll need to remove it;)

You could try this:

SELECT
  SUM(
    LENGTH(TRIM(REGEXP_REPLACE(post, '<[^>]+>', ''))) - LENGTH(REPLACE(TRIM(REGEXP_REPLACE(post, '<[^>]+>', '')), ' ', '')) + 1
  ) AS total_word_count
FROM forums_posts
WHERE TRIM(REGEXP_REPLACE(post, '<[^>]+>', '')) != '';

to get the total word count from all forum posts (this DB query doesn't contain any filtering for only visible, published ones)

Hi Daniel,

Thank you for that, looks like that would work great for the forum posts -- presumably that would work on blog posts, blog comments, and status replies as well? I'm not familiar with the Club database structure to know what would be involved with that are of the forum.

Unfortunately, as I am now using Invision's cloud hosting service, I am unable to run queries myself (at least as far as I can tell). I'd be greatly appreciative if Invision could run these queries against my database. This is just a one-time thing, just trying to gather some statistics about the forum for a project we're working on.

15 hours ago, Daniel F said:

to get the total word count from all forum posts (this DB query doesn't contain any filtering for only visible, published ones)

Yeah, that is fine for my needs, this doesn't need to be any more complicated.

Thank you again!