SQL: Quickly count instances of a variable within a string.

Filed under: Quick Code, SQL Server — Written by Chrissy on Thursday, April 27th, 2006 @ 8:23 am

Using SQL and want to know how many times 'http' appears in a string using about one line of code? Find out by creatively using the replace() function.

Declare @myStr varchar(1500), @countStr varchar(100)
Set @myStr = 'http://www.buydrugshere.com go here! http://www.onlinemortage.com right there!'
Set @countStr = 'http'
Select (len(@myStr)-len(replace(@myStr,@countStr,'')))/len(@countStr) as theCount

This method is effective in any language that has a replace function.

2 Comments   -
  • Comment by Adam Tuliper | July 31, 2007 @ 10:55 pm

    neat trick. beware of cpu cost for large strings, but nifty trick indeed.

  • Comment by Tihomir Piskuliyski | October 31, 2007 @ 11:08 am

    Very nice. This trick made my day. I was trying to come up with something like this all day.

RSS feed for comments on this post. TrackBack URL

Leave your comment