Function to find the number of times a pattern is repeated.

CREATE FUNCTION pattern_count(INSTRING VARCHAR2, INPATTERN VARCHAR2)

RETURN NUMBER

IS

COUNTER NUMBER;

NEXT_INDEX NUMBER;

STRING VARCHAR2(2000);

PATTERN VARCHAR2(2000);

BEGIN

COUNTER := 0;

NEXT_INDEX := 1;

STRING := LOWER(INSTRING);

PATTERN := LOWER(INPATTERN);

FOR I IN 1 .. LENGTH(STRING) LOOP

IF (LENGTH(PATTERN) <= LENGTH(STRING)-NEXT_INDEX+1)

AND (SUBSTR(STRING,NEXT_INDEX,LENGTH(PATTERN)) = PATTERN) THEN

COUNTER := COUNTER+1;

END IF;

NEXT_INDEX := NEXT_INDEX+1;

END LOOP;

RETURN COUNTER;

END;