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 theCountThis 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 master's degree in Systems Engineering. Chrissy is also certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.
join chrissy on twitch
neat trick. beware of cpu cost for large strings, but nifty trick indeed.
Very nice. This trick made my day. I was trying to come up with something like this all day.
Excellent bit of SQL that, cheers ;)