use junk

create table #test
(
[id] int not null
,[value] text null
)
go


insert #test ([id], [value])
select 1, 'blah. blah.blah ABC blah blah ABC' union all
select 2, 'ABC blah ABC DEFG blah blah blah' union all
select 3, 'blah blah blah blah HAHA blah blah' union all
select 4, NULL


select sum((len(v) - len(replace(v, 'ABC', '')))/3)
from   (
       select convert(varchar(8000), [value]) v from #test
       ) d
go
drop table #test

OUTPUT:
-----------
4

create table #test
(
[id] int not null
,[value] text null
)
go


insert #test ([id], [value])
select 1, 'blah. blah.blah ABC blah blah ABC' union all
select 2, 'ABC blah ABC DEFG blah blah blah' union all
select 3, 'blah blah blah blah HAHA blah blah' union all
select 4, NULL

select sum((len(v) - len(replace(v, 'ABC', '')))/3) as [totalcount<=8000]
from   (
       select convert(varchar(8000), [value]) v 
       from #test
       where datalength([value]) <= 8000
       ) d

----------------------------------------
--for values > 8000
declare @i int
       ,@totcnt int
       ,@id int
       ,@pos int
       ,@str varchar(200)
set @str = 'ABC'

select @id = min([id])
       ,@totcnt = 0
from #test
--where datalength([value]) > 8000 

while @id is not null
begin
       --first check in current row
       select @i = patindex('%' + @str + '%', [value])
              ,@pos = @i
       from #test 
       where  id = @id

       --if any found then increment the counter and look for another occurance
       while @i > 0
       begin
              --increment count
              set @totcnt = @totcnt + 1

              --look for index of next occurance starting from first character after last occurance
              select @i = patindex('%' + @str + '%', substring([value], @pos + len(@str) + 1, datalength([value])))
              from   #test
              where  id = @id
              
              set @pos = @pos + @i
       end

       --go to next row
       select @id = min([id]) 
       from #test 
       where id > @id
       --and datalength([value]) > 8000 
end
select @totcnt [totalCount>8000]

go
drop table #test

OUTPUT:
totalcount<=8000
----------------
4

totalCount>8000
---------------
4