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

Question

I have a column that has values formatted like a,b,c,d. Is there a way to count the number of commas in that value in T-SQL?

Answer 1

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, ',', ''))

Answer 2

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

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

Answer 3

Perhaps you should not store data that way. It is a bad practice to ever store a comma delimited list in a field. IT is very inefficient for querying. This should be a related table.

Answer 4

DECLARE @records varchar(400)
SELECT @records = 'a,b,c,d'
select  LEN(@records) as 'Before removing Commas' , LEN(@records) - LEN(REPLACE(@records, ',', '')) 'After Removing Commans'

Answer 5

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

CREATE FUNCTION dbo.CountOccurancesOfString
(
    @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.CountOccurancesOfString(MyColumn, 'MyString') = 1