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.

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, and holds a number of certifications, including those relating to SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SQL Server
3 comments on “SQL: Quickly count instances of a variable within a string.
  1. Adam Tuliper says:

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

  2. Tihomir Piskuliyski says:

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

  3. T Bentley says:

    Excellent bit of SQL that, cheers ;)

Leave a Reply

Your email address will not be published. Required fields are marked *

*