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.