Count String Occurance

(length(str1) - length(replace(str1, str2)))/length(str2)
gives how many times str2 appears in str1.

e.g.

select (length('xyzabc123abcefgh') - length(replace('xyzabc123abcefgh', 'abc')))/length('abc') from dual

OCCURANCE
----------
2