multiple occurances of string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

multiple occurances of string

Author

Topic

helpimstuck
Starting Member

2 Posts

Posted - 2004-11-02 : 10:55:50

Hello,
I'd like to find how many occurances of 'http' there are in a string (damn spam).

ie http://www.buydrugshere.com go here! http://www.onlinemortage.com right there! http://www.viagra.com buy that!

would return "3"

Is there a way to pull it off in one line? Ive tried getting creative with patindex but it's just not working.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-02 : 11:00:33

This should do it...


Declare @myStr varchar(1500),
	@countStr varchar(100)

Set @myStr = 'http://www.buydrugshere.com go here! http://www.onlinemortage.com right there! http://www.viagra.com buy that!'
Set @countStr = 'http'

Select (len(@myStr)-len(replace(@myStr,@countStr,'')))/len(@countStr)

Corey