AFAIK most SQL servers have some equivalent of a LEN() function.
Edit: Better yet, try something like this:
SELECT left(post_date, 7) as month,
ROUND(AVG(CHAR_LENGTH(post_title)), 0) as characters
FROM `wp_PREFIX_HERE_posts`
WHERE post_type = "post"
GROUP BY left(post_date, 7)
ORDER BY left(post_date, 7)
This will provide you an average post title length by month.
Edit 2: Incidentally, here's a chart of article and blog title lengths for articles and blog entries on a moderately well-read civic affairs website I edit: http://min.us/mvfTrxx
Agreed. It would be interesting to also see 'average characters per word', to see whether this has DECREASED as the number of words has increased - almost a 'dumbing down', so that the title can still fit into the 66-70 character Google limit (before it cuts-off the result title in the search results pages).
Probably, but I'm still not convinced that blogs overall have trended toward lengthier titles. I'm more willing to hypothesize that writers over the past few years consider the variables in context for their writing.
What may make sense as a package deal on a single page may look nonsensical in a Google search or a twitter post.
Longer titles may simply be an occasional consequence of this.
Yes, but the implications are very interesting I think. There'll be demand for new solutions eventually. Both on Search-side and on the micro-blogging front. What will those solutions be?
I wonder if I'm the only one irked that the Techcrunch graph's vertical scale was in words and the Mashable graph was scaled in characters. If the goal is to show a link between longer post titles and Twitter, wouldn't it make sense to scale them both in characters? As it is, I can't really draw any conclusions from the Techcrunch graph at all.
Also, it would have been helpful to have a control sample of, say, random blogs from some of the major blogging services to compare to the other two.
So I did a quick survey of my blog ( http://www.puremango.co.uk ) which has been running for 6 years.
Here is a graph of the results: http://i.imgur.com/l2iKC.png
There's a slight upward trend, but I'm certainly not seeing as direct a correlation as there is on techcrunch (but then they have more data)
So perhaps this is the result of a particular editorial style rather than a wider trend. Worth investigating though.
If you have a wordpress blog, here's the SQL I used:
Then I just took it into excel with the following formula to count words: