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

27Apr/063

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

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.

Posted by: Chrissy Filed under: SQL Server Leave a comment

Comments (3) Trackbacks (0) ( subscribe to comments on this post )

Adam Tuliper
July 31st, 2007 - 22:55

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

( REPLY )
Tihomir Piskuliyski
October 31st, 2007 - 11:08

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

( REPLY )
T Bentley
July 6th, 2009 - 07:13

Excellent bit of SQL that, cheers

( REPLY )