How to count the occurrences of a string inside TEXT datatype in sql server 2005

Question

There is a column of datatype TEXT in my table. Now, I need to find the number of occurrences of a string in that TEXT field. I have already created a Full-text index on that table. But I don't know how to proceed further. I already found ways to count string occurrences for VARCHAR. But they cannot be applied AS IS to TEXT field. Any suggestions?

Answer 1

convert that(Text) field to varchar and find the count

e.g : convert(varchar(200),textColumn)

Answer 2

Try this:

 declare @searchString varchar(max);
 set @searchString = 'something';
 declare @textTable table (txt text);
 insert into @textTable ( txt )
 values  ( 'something that has something 2 times' )

 select 
 (
  datalength(txt) - 
  datalength(replace(cast(txt as varchar(max)), @searchString, ''))
 )
 /datalength(@searchString) [Count]

   from @textTable as tt

Note that casting as varchar(max) won't truncate your text column as the max length of varchar(max) is 2^31-1 bytes or 2Gb.