function to count occurences of substring in a string

Hi Experts,

I'm looking for a function to count occurences of a word / substring in a string...
I have seen this - http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/counting-occurrences-of-a-substring-within-a-string.aspx but I was hoping for something a bit simpler.

Many thanks!
  Dmitri
CallConnectionAsked:
Who is Participating?
 
TimCotteeConnect With a Mentor Commented:
If you want an exact count then:

Declare @Match varchar(100)
Set @Match='Hello'
Select (DataLength(MyField) - DataLength(Replace(MyField,@Match,''))) / DataLength(@Match)

For example.
0
 
CallConnectionAuthor Commented:
Thanks, here's My SQL version:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetSubstringCount](@s varchar(8000), @countthis varchar(8000))
RETURNS INT --WITH SCHEMA BINDING AS
BEGIN	
	RETURN 	
	(		SELECT (len(@s)-len(replace(@s,@countthis,'')))/(case when len(@countthis) = 0 then 1 else len(@countthis) end)
	)
END

Open in new window

0
 
CallConnectionAuthor Commented:
Tim,
  Your principle was right - I did my version at the same time..
Cheers,
  Dmitri
0
 
CallConnectionAuthor Commented:
I was hoping for a function, - but thank you vey much for the code.
0
All Courses

From novice to tech pro — start learning today.