Systems Engineering and RDBMS

Doing a word count in SQL

Posted by decipherinfosys on February 4, 2009

Yesterday, while working on a UI design issue, for a big string the BA was looking for a count of the words in the string.  Now, he used the MS Word functionality to do the word count.  It can be done pretty simply in SQL as well.  Here’s an example:

declare @x nvarchar(100)

set @x = N’How many words are there in this big string?’

select len(@x) – len(replace(@x, ‘ ‘, ”)) + 1

And the answer would come out to be 9.  All we did was get the length of the string and then subtracted from it the length of the string after replacing all the empty spaces – we have to add 1 to account for the first word.  Also, note that we used the function len() instead of datalength() – you can read the difference between the two functions over here.

One Response to “Doing a word count in SQL”

  1. Simon said

    This is great, I have been looking for something like this for a while. Thanks for sharing!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: