Counting strings

I posted this:
http://www.oracle.com/technology/oramag/code/tips2005/022105.html


Suppose we want to find out how many times 'aa' is repeated in a string, for example 'aakhgghghjaahhjghghaajghgaa', using SQL only. The SQL statement below can easily give the answer:



SELECT (length('aakhgghghjaahhjghghaajghgaa') -
length(REPLACE('aakhgghghjaahhjghghaajghgaa','aa')))/length('aa')
FROM dual


The code can be easily modified to extract the number of times any pattern is repeated in any string.

Ilya Petrenko, a Sr.DBA at ICT Group Inc., in Newtown, PA. however came up with a neater solution:

http://www.oracle.com/technology/oramag/code/tips2005/051605.html