Fulltext - How to get a word hitcount

Saw this on the newsgroups and thought it was really neat. To get the number of times a word occurs in a column you can do

SELECT PKCol,
hitcount=(len(TextCol)-len(replace(TextCol,'WordToFind','')) )/len('WordToFind')

FROM myTable

WHERE CONTAINS (TextCol, 'WordToFind')

I also saw another solution using a CTE but I think this has a better performance


-
Published 01 November 2006 21:11 by simonsabin

Comments

03 November 2006 09:58 by Julian Kuiters - Count the Occurrence of a Phrase or Word in a Column (SQL Server 2005)

# Julian Kuiters - Count the Occurrence of a Phrase or Word in a Column (SQL Server 2005)

PingBack from http://www.julian-kuiters.id.au/article.php/sqlserver2005-phrase-word-occurance