Word Count

12th_Man · July 25th, 2003, 08:01 AM

Quick question:

If I have a table with one of the columns being the text of a message, what is the easiest way to do a query that returns a couple fields out of the table and the number of words in the message? Is this best done client or server side?

owain · July 25th, 2003, 08:17 AM

I would have said client-side. You are not querying a database, you are programmatically parsing a particular field.

That field may have been returned using a server-side query, but you are processing the field on the client.

If you need to do queries like the one you suggested on the server, then maybe you need to consider how you have normalised your data.

It does look like you just need to do it as a one off though, so your data may be perfectly normalised - I leave that distinction to you :)

Regards
Owain Williams

12th_Man · July 25th, 2003, 08:26 AM

Ok, thanks for the input. I would prefer to do it in SQL, but I think you are right. I'll do it on the client.

Thanks again for the advice.

pgtips · July 25th, 2003, 08:53 AM

There's a quick kludge to do stuff like this, depends how bothered you are about the intricacies of what makes up a word. If we assume that each word is separated by a space, but there won't be a space at the end of the final word, then all you need to do is count the number of spaces in the string and add 1.

Code:

SELECT (1 + LEN(Field) - LEN(REPLACE(Field, ' ', ''))) AS WordCount

owain · July 25th, 2003, 08:56 AM

Will then LEN and REPLACE functions work in SQL Server (or other RDBMS's for that matter) or are they Access only functions?

Regards
Owain Williams

pgtips · July 25th, 2003, 08:57 AM

I never even thought about Access! LEN and REPLACE are available in SQL Server.

12th_Man · July 25th, 2003, 09:03 AM

Thank you pgtips. I was trying to think of a way to replace all non space characters and then get the len of the remaining spaces, but yours is much easier.

The only problem I can see is that when people put 2 spaces at the start of a sentence, but it doesn't have to be totally exact.

pgtips · July 25th, 2003, 09:07 AM

Hey, like I said its a kludge :).

There is another problem - empty strings have a word count of 1! You can fiddle around with this stuff to your heart's content, e.g. replace double spaces with one space before doing the count, and do a separate test for empty strings. The point is its an easy way to get an approx word count...