Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Count Individual Words in freetext fields Expand / Collapse
Author
Message
Posted Friday, February 11, 2005 6:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 15, 2013 3:14 AM
Points: 247, Visits: 215

Intresting bit of help required would prefer to do this in SQL especially as now one is working on the server.

I have a table(activity) with 2 fields  (RowId,notes)

What I want is a count on all the unique words in the notes field

so for example

rowid              

x11102 =This is the first test

x11103 =This is the Second test

------------------

Id want my result set like

Word      Count

-----------------

This          2

is              2

the            2

First          1

Test           2

Second        1

Any help would be much appreciated

 




Post #161089
Posted Friday, February 11, 2005 12:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 7, 2012 1:07 PM
Points: 222, Visits: 22

A simple, brute force strategy would be to write a parser to read each NOTES and split the words into a single vertical table.  Then simply run a count for each of the distinct words in the vertical table.  An alternative implementation is to have two columns in the vertical table (WORD and COUNT) and just update count if the word already exists.

The same results can be achieved by using one of the "split" functions available on this site inside of a derived subquery.

Have a good day

Wayne




Post #161196
Posted Saturday, February 12, 2005 3:23 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:37 AM
Points: 1,140, Visits: 703
The problem with most split functions is that they can handle only a single character, e.g. ',' -- I think you would have to modify it to handle a pattern instead, so that you could consider all punctuation, white space, etc, characters.

--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #161296
Posted Tuesday, February 15, 2005 3:02 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:37 AM
Points: 1,140, Visits: 703
Okay, I just posted a UDF to help solve this problem:

http://sqljunkies.com/WebLog/amachanic/articles/PatternSplitString.aspx

... To use it for this problem, you would have to cursor/loop through the table for each row you want, insert the words into a temp table, and then take the final count from there. I'm not aware of a set-based way to handle the problem.


--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #161880
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse