f_countchar

CREATE OR REPLACE FUNCTION f_countchar (p_string VARCHAR2, p_pattern VARCHAR2)
   RETURN NUMBER
IS
   v_counter      NUMBER;
   v_next_index   NUMBER;
   v_string       VARCHAR2 (1000);
   v_pattern      VARCHAR2 (1000);
BEGIN
 IF p_string IS NOT NULL AND p_pattern IS NOT NULL THEN
   v_counter := 0;
   v_next_index := 1;
   v_string := LOWER (p_string);
   v_pattern := LOWER (p_pattern);

   FOR i IN 1 .. LENGTH (v_string)
   LOOP
      IF     (LENGTH (v_pattern) <= LENGTH (v_string) - v_next_index + 1)
         AND (SUBSTR (v_string, v_next_index, LENGTH (v_pattern)) = v_pattern
             )
      THEN
         v_counter := v_counter + 1;
      END IF;

      v_next_index := v_next_index + 1;
   END LOOP;
   RETURN v_counter;
 ELSE
   RETURN 0;
 END IF;
END;
/

SELECT f_countchar('BADRUL ALAM BADRUL ALAM','ALAM') FROM DUAL