Count number of strings within a string

,

This function will return the number of occurrences of one string within another string. I was looking for something like this recently, and the only thing I could find was needlessly complicated. Don't forget, when calling a scalar function, you must qualify with username like this:

Select dbo.StringCount('Hi There','e')

This function may be a little too simple for your needs, but, you can modify and use any way you like.  You could also easily create a procedure, with or without output parameter, with the same code.  Enjoy.

CREATE Function StringCount 
(@SearchString Varchar(8000),
@SearchFor Varchar(255))
Returns Int
As
Begin
Declare 
@Position Int,
@Counter  Int

Set @Position = 0
Set @Counter  = 0

While (Charindex(@SearchFor,@SearchString) > 0)
    
Begin

Select @Counter = (@Counter + 1), 
@Position = (Charindex(@SearchFor,@SearchString) + 1)

Select @SearchString = 
Substring(@SearchString,@Position,Len(@SearchString))

End
    
Return @Counter

End
go

Rate

Share

Share

Rate