Weekly post on Oracle Web site (TIP for Week of December 13, 2004)

Oracle
Calculate the number of times a string appears in another string
Aui de la Vega, Phil
CREATE FUNCTION num_chars (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) THEN
COUNTER: = counter+1;
End IF;
Next_index: = next_index+1;
End LOOP;
return COUNTER;
End;
Password Analysis:
The code is not very complex, the use of Next_index plus an increase, to instring, from the instring string of the first Next_index characters to intercept
Length (pattern) long string, if the intercepted string is equal to pattern, the calculator counter+1, in Pl/sql developer+oracle10+winnt
Try to succeed.