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