Count number of times substring occurs in a column

Here is a tip to show how to count number of times a substring occurs in a field with simple Transact SQL.

This functionality is pretty common in languages like VB, but in SQL there#%92s no obvious solution. CharIndex will show that a substring exists, but not how many times.

This functionality can be very valuable when trying to deal with de-normalized data, such as when you have comma-delimited values inside a field.

Here#%92s what I came up with: use the replace function to return a string that has an extra space in it for every occurrence of the substring, then compare the length of the new string to the original. It should be one character longer for each occurrence.

In the example below I include a count of the substring (a delimiting comma):, as well as the values. Your query might be cleaner if nulls aren#%92t allowed.