Oracle: counting the number of substrings in a string?

Question

How do I count the number of occurrences of a substring inside of a string?

Answer 1

As of version 11g, regexp_count will do this.

select regexp_count('abba', 'b') from dual;
   2 
select regexp_count('abba', 'b+') from dual;
   1 

Answer 2

In pre-11g version you can:

select (length(string) - length(replace(string,substring,''))) / length(substring) as occ
from dual;

The idea is to see how much space in the string is occupied by the substring, then, to see how many times the substring is in that space just divide to the length of substring.