Counting occurrence of substring in SQL
One of the things that I do quite a few times is finding how many a certain substring occurs in SQL varchar field.
The substring is mostly a comma in a field which is comma delimited.
Knowing the number of commas in the substring will help count the number of elements because you then just add 1 and this is the example I will show below.
For an example, let me consider a list of months say in the Months field, say January, February, March, August, December and comma separated.
To count how many months in this list I find it easy to use the following query assuming in this case that the months are in the variable @months.
declare @months varchar(100)
declare @countMonths int
set @months = 'January, February, March, August, December'
SELECT @countMonths = (LEN(@months) - LEN(REPLACE(@months,',','')) + 1 )
print @countMonths
In this case @countMonths will be shown as 5.
delicious | 
digg
