SQLTeam.com | Weblogs | Forums

How to count number of instances of each word throughout the Comment column - Text Analytics

#1

I have to count the number of instances of each word in the Comments column throughout the table using SQL Server. Eg:
Row# Comments
1 I like working on SQL
2 I enjoy sleeping and like watching TV

So the output should be :
Word Count
I 2
like 2
working 1
on 1
SQL 1
.......

Could someone please suggest if there is a way to get this count using MS SQL?

Thanks!

#2

I would suggest using a splitter to separate the words into a table then count.

#3

This might work for you:

with cte
  as (select [row#]
            ,case when comments like '% %'
                then left(comments,charindex(' ',comments)-1)
                else comments
             end as word
            ,case when comments like '% %'
                then substring(comments,charindex(' ',comments)+1,len(comments))
                else ''
             end as rest
        from yourtable
      union all
      select [row#]
            ,case when rest like '% %'
                then left(rest,charindex(' ',rest)-1)
                else rest
             end as word
            ,case when rest like '% %'
                then substring(rest,charindex(' ',rest)+1,len(rest))
                else ''
             end as rest
        from cte
       where rest!=''
     )
select word
      ,count(*) as [count]
  from cte
 group by word
;