How do you count the number of occurrences of a certain substring in a SQL varchar?

Question

The first way that comes to mind is to do it indirectly by replacing the comma with an empty string and comparing the lengths

Declare @string varchar(1000)
Set @string = 'a,b,c,d'
select len(@string) - len(replace(@string, ',', ''))

Quick extension of cmsjr's answer that works for strings with more than one character.

CREATE FUNCTION dbo.CountOccurrencesOfString
(
    @searchString nvarchar(max),
    @searchTerm nvarchar(max)
)
RETURNS INT
AS
BEGIN
    return (LEN(@searchString)-LEN(REPLACE(@searchString,@searchTerm,'')))/LEN(@searchTerm)
END

Usage:

SELECT * FROM MyTable
where dbo.CountOccurrencesOfString(MyColumn, 'MyString') = 1

You can compare the length of the string with one where the commas are removed:

len(value) - len(replace(value,',',''))

The answer by @csmjr has a problem in some instances.

His answer was to do this:

Declare @string varchar(1000)
Set @string = 'a,b,c,d'
select len(@string) - len(replace(@string, ',', ''))

This works in most scenarios, however, try running this:

DECLARE @string VARCHAR(1000)
SET @string = 'a,b,c,d ,'
SELECT LEN(@string) - LEN(REPLACE(@string, ',', ''))

For some reason, REPLACE gets rid of the final comma but ALSO the space just before it (not sure why). This results in a returned value of 5 when you'd expect 4. Here is another way to do this which will work even in this special scenario:

DECLARE @string VARCHAR(1000)
SET @string = 'a,b,c,d ,'
SELECT LEN(REPLACE(@string, ',', '**')) - LEN(@string)

Note that you don't need to use asterisks. Any two-character replacement will do. The idea is that you lengthen the string by one character for each instance of the character you're counting, then subtract the length of the original. It's basically the opposite method of the original answer which doesn't come with the strange trimming side-effect.