Almost too trivial for a post

I have been using SQL for years and from time to time I stumble upon a problem, where I needed to count the occurrence of a character or a string within another string. Searching for a standard function in Oracle gives me no results. Browsing in the good old SQL forum gave me an idea on how to create my own function:

CREATE OR REPLACE FUNCTION string_occurrence (
   p_string      IN   VARCHAR2,
   p_substring   IN   VARCHAR2
)
   RETURN NUMBER
IS
   v_occurrence   NUMBER;
BEGIN
   v_occurrence :=
        (  LENGTH (p_string)
         - NVL (LENGTH (REPLACE (p_string, 
                                 p_substring, '')), 0)
        )
      / LENGTH (p_substring);
   RETURN v_occurrence;
END string_occurrence;

The simplicity of the idea is fascinating:

1. since you can't count it directly, you count the total string,

2. after that you replace the string you search for with '' (nothing) and count again,

3. you subtract the second value from the first and

4. you divide the result by the length of the string you want to count the occurrence for.

And there we go.