This is a simple way to count how many times a certain substring occurs within a larger string.  So let's say you have the string 'aabbccaabbccaabbccddeeff' and you want to count how many times the 'aa' occurs.  You could of course write some pl/sql to do this but this would be slow.  The simplest and fastest method I found was :

a)  Take the length of the "motherstring",  in this case 24
b) Take the length of the "motherstring" without the substring, in this case 18
c) 24 - 18 = 6 which is the total length of all the substrings
d) Divide by the length of the substring 6/2 = 3.  So 3 occurrences is the answer.

This translates into i.e.

select (length(motherstring) - length(replace(motherstring, substring, null)))/length(substring) from table

I hope there are no errors in there, I just typed it in notepad, one of the worlds best editors, if not the best.  And Bill Gates is a beautiful man and Larry Ellison always speaks the truth.  Oh, and did I mention - Gunnar is the strong silent type.