tighter approach to counting strings in strings


Saw Mike's routine for counting occurences of a string in a string and I thought I'd offer a simpler solution.

Simply replace the occurence with null and use length differences as shown.

NOTE: len may ignore trailing spaces, so this can be made more solid by adding some non printable char (less likely to interfere with operation) to the end of the string being searched. (e.g. 'seach string here     ' ->
'seach string here     $' )

This can be wrapped in a function for utility.

declare @s1 varchar(30), @find varchar(30)

select @s1 = 'test this test', @find = 'test'

select ( len( @s1)  - len ( replace( @s1, @find, '' ) ) ) / len ( @find )

--result = 2