This time the fun is in counting how many times one string occurs inside another. Of course this has been attempted many times - kudos to all who've attempted it before me! My function, however, includes an extra parameter to support case-sensitive comparisons. Here it is:
create function dbo.fnCount_StringInText
 (
 @text  nvarchar(4000)
 ,@string nvarchar(4000)
 ,@caseSensitive bit  = null
 )
returns int
as
begin
 declare @count int

 if (datalength(@string) != 0)
  begin
   if (@caseSensitive = 0 or @caseSensitive is null)
    begin
     set @count
       = (datalength(@text)
       - datalength(replace(@text collate Latin1_General_CI_AS, @string, N'')))
       / datalength(@string)
    end
   else
    begin
     set @count
       = (datalength(@text)
       - datalength(replace(@text collate Latin1_General_CS_AS, @string, N'')))
       / datalength(@string)
    end
  end
 else
  begin
   set @count = 0
  end

 return @count
end
go
A few examples of use:
declare @text varchar(4000)
set @text = N'Round the rough and rugged rocks Roscoe the rabbit rudely ran.'
  • To count the R's regardless of case:
  • select dbo.fnCount_StringInText(@text, N'R', null) as Result
  • To count only the capital R's:
  • select dbo.fnCount_StringInText(@text, N'R', 1) as Result
ML