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 Wednesday, November 1, 2006 9:11 PM by simonsabin

Comments

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