Count of perticular word in table

I have table with columns (Id int, Userid int, LogData Varchar(500), Date DateTIme) containing data in LogData as,
Data Mining
Web Mining
Website for data mining

I want a result as,
Word              Count
Data                   3
Mining               3
Web                   1
Website             1
For                     1
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

A simple count aggregate function should do the job

I am copying the appropriate query here (Replace Table1 with the appropriate table name):

SELECT LogData as WORD, Count(LogData) as WordCount
FROM Table1
Group By LogData;

Open in new window

Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:

This is thing is called as frequency of words. You better have to create list of tokenes/words from the given text in temp table or in memory table.
then you have to loop/cursor for each token against the text. you can use CharIndex(str1, str2, startIndex) funtion for this.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
There is one more way to do this by using group by.
Create List of all tokens and insert into the memory table
Then query the memory table as following:

      token  VARCHAR(500)      
 -- add all tokens into @Tokens, for this you can use newline, punctuations and space as delimeter

SELECT token, COUNT(*) AS Frequency
FROM @Tokens  
GROUP BY token
Order by Frequency
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Try this

select DATA, COUNT(Data) Cnt from dbo.Split((SELECT LogData AS [data()] FROM tableName FOR XML PATH('')), ' ') group by Data

Open in new window

split function

	@RowData nvarchar(2000),
	@SplitOn nvarchar(5)
RETURNS @RtnValue table 
	Id int identity(1,1),
	Data nvarchar(100)
	Declare @Cnt int
	Set @Cnt = 1

	While (Charindex(@SplitOn,@RowData)>0)
		Insert Into @RtnValue (data)
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))


Open in new window

Here is another option:
declare @Table table (id int identity, LogData varchar(500))

insert into @Table values('Data')
insert into @Table values('Data Mining')
insert into @Table values('Web Mining')
insert into @Table values('Website for data mining')

Select Word, Count(1) Count
  From (
        select l.w.value('.','varchar(20)') Word
         Select cast('<w>' + replace(logdata, ' ', '</w><w>') + '</w>' as xml) as LogData 
           From @Table
        ) as t(Words)
        cross apply Words.nodes('w') l(w)
       ) as words
group by word

Open in new window

Word                 Count
-------------------- -----------
Data                 3
for                  1
mining               3
Web                  1
Website              1

(5 row(s) affected)

Open in new window

Try this

select val, COUNT(val) cnt from (
  r.value('.', 'nvarchar(100)') as Val
from (select cast('<r>'+replace((SELECT LogData AS [data()] FROM tableName FOR XML PATH('')), ' ', '</r><r>')+'</r>' as xml)) as x(x)
  cross apply
    x.nodes('r') as r(r)
group by val

Open in new window

For the original question, would you ever need to count the word 'site'? Because "Website" contains 'site', are you concerned that it might be counted? (Or that it might be missed?)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.