How do you count the number of occurrences of a certain substring in a SQL Server varchar?
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 a 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 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. */