How to sort or bulk delete posts based on the number of words?

Needless to say, you absolutly must backup your database before running any query such as this. Untested, but either way should do the trick.

From PHP –

global $wpdb;

$wpdb->prepare('DELETE FROM %1$s WHERE LENGTH('post_content') < 300', $wpdb->posts)
$wpdb->query($query);

From MySQL –

DELETE FROMwp_postsWHERE LENGTH('post_content') < 300;

I’ve found the plugin that show the number of words in the Admin’s Posts Edit page. There were two plugins that can do this, the Admin Word Count plugin and Admin Word Count Column plugin.

However, both of them don’t have the sorting ability. They just display the number of words for each post, which means that it would still be a lot of work to get rid all the posts with less than 300 words.

duck_boy: thank you! I’ll try them and let you know the result..

Just a thought – you may want to add on to the end of the query –

AND post_type = 'post'

Doing this will ensure only your posts are deleted, not you pages, media, etc.

Hello duck_boy

I really appreciate your replies. There are some things I want to confirm:

Does your line of command true? So it is
DELETE FROMwp_postsWHERE LENGTH(‘post_content’) < 300;
instead of
DELETE FROM wp_posts WHERE LENGTH(‘post_content’) < 300; ?

I also want to know if I should leave the default command or just delete this line below and use yours:
SELECT * FROM wp_posts WHERE 1

Oh, and one more thing. From my newbie understanding, the command LENGTH will count the character, instead of the words. Is that correct?

I recently google for it and came up with the command below:

DELETE FROM wp_posts WHERE SUM( LENGTH(post_content) – LENGTH(REPLACE(post_content, ‘ ‘, ”))+1) < 300;

However, it didn’t work out and the program give me this error message:
#1111 – Invalid use of group function

Is there any suggestion to fix this?

I see some formatting issues in my post above. This should do the trick, but as I’ve said before, you should absolutly 100% backup your DB, that way you can just restore it should this not be what you are looking for.

And yes, delete what is automatically there and run what is below.

DELETE FROM wp_posts WHERE 1=1 AND LENGTH('post_content') < 300 AND post_type = 'post';

How about my previous post, duck_boy? What do you think of that?

From my newbie understanding, the command LENGTH will count the character, instead of the words. Is that correct?

Yes, LENGTH will count the characters as opposed to the letters.

I’ve never done what you are trying now, but you shouldn’t need SUM() I don’t think. Try this (and backup your database!).

DELETE FROM wp_posts WHERE 1=1 AND LENGTH(REPLACE('post_content', ' ', '')) < 300 AND post_type = 'post';

I’ve tried your code, Duck_boy, and all of my posts are gone… even though a lot of them are more than 300 words.
I’m currently restoring my database. But thanks again for your advice and reply…