How do you count the number of occurrences of a certain substring in a SQL Server varchar, and why would you need to do this? One example is that you need to verify incoming file data and you need to raise an error if you do not find a certain number of characters in a file. For example, if you are reading a CSV file, you would expect that there are commas in the file.

Suppose you are bulk importing a CSV file. Each row of the file must have the same number of commas. It doesn’t matter if the file has a header row, a detail row, and a trailer row. The number of commas on each row must be the same.

Therefore you can use the modulo function. If you expect the number of commas to be four on each row, you can divide the number of commas found by 4. If the remainder is zero, then the number of commas is either 4, 8, 12, 16, 20 and so on, which is good. However, You need to also check that number of commas is not zero, otherwise, you could raise an error.

DECLARE @string VARCHAR(1000)
DECLARE @NumberCommas INT
DECLARE @ExpectingMultiplesOf INT
DECLARE @Remainder INT
SET @ExpectingMultiplesOf = 4;
SET @string = 'Hello,6,Bob,,w,'
SELECT @NumberCommas = LEN(REPLACE(@string, ',', '**')) - LEN(@string)
SET @Remainder = @NumberCommas % @ExpectingMultiplesOf
IF @NumberCommas <> 0
	BEGIN
		PRINT 'Remainder is ' + cast(@Remainder AS VARCHAR)
		IF @Remainder <> 0 
			BEGIN
				PRINT 'Error. Expecting multiples of ' + cast(@ExpectingMultiplesOf AS VARCHAR) + 
				' commas. Each row must contain exactly 4 commas. Found ' + cast(@NumberCommas AS VARCHAR) + ' comma(s).'
			END
	END
	ELSE
	BEGIN
		PRINT 'Error. No commas found. Not a CSV file.'
	END
/*
Note that you do not 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.
*/