create or replace FUNCTION freq_instr1 (string_in IN VARCHAR2, substring_in IN VARCHAR2, match_case_in IN VARCHAR2 := 'IGNORE') RETURN NUMBER /* || Parameters: || string_in - the string in which frequency is checked. || substring_in - the substring we are counting in the string. || match_case_in - If "IGNORE" then count frequency of occurrences || of substring regardless of case. If "MATCH" then || only count occurrences if case matches. || || Returns the number of times (frequency) a substring is found || by INSTR in the full string (string_in). If either string_in or || substring_in are NULL, then return 0. */ IS -- Starting location from which INSTR will search for a match. search_loc NUMBER := 1; -- The length of the incoming substring. substring_len NUMBER := LENGTH (substring_in); -- The Boolean variable which controls the loop. check_again BOOLEAN := TRUE; -- The return value for the function. return_value NUMBER := 0; BEGIN IF string_in IS NOT NULL AND substring_in IS NOT NULL THEN /* Loop through string, moving forward the start of search. || The loop finds the next occurrence in string_in of the || substring_in. It does this by changing the starting location || of the search, but always finding the NEXT occurrence (the || last parameter is always 1). */ WHILE check_again LOOP IF UPPER (match_case_in) = 'IGNORE' THEN -- Use UPPER to ignore case when performing the INSTR. search_loc := INSTR (UPPER (string_in), UPPER (substring_in), search_loc, 1); ELSE search_loc := INSTR (string_in, substring_in, search_loc, 1); END IF; check_again := search_loc > 0; -- Did I find another occurrence? IF check_again THEN -- Increment return value. return_value := return_value + 1; -- Move the start position for next search past the substring. search_loc := search_loc + substring_len; END IF; END LOOP; END IF; RETURN return_value; END freq_instr1; / create or replace FUNCTION freq_instr2 (string_in IN VARCHAR2, substring_in IN VARCHAR2, match_case_in IN VARCHAR2 := 'IGNORE') RETURN NUMBER IS substring_loc NUMBER; return_value NUMBER := 1; BEGIN LOOP IF UPPER (match_case_in) = 'IGNORE' THEN /* || Use the last argument to INSTR to find the Nth occurrence of || substring, where N is incremented with each spin of the loop. || If INSTR returns 0 then have one too many in the return_value, || so when I RETURN it, I subtract 1 (see code following loop). */ substring_loc := INSTR (UPPER (string_in), UPPER (substring_in), 1, return_value); ELSE substring_loc := INSTR (string_in, substring_in, 1, return_value); END IF; /* || This is an "infinite" loop. I need EXIT to force the || loop to terminate when no more occurrences are found. */ EXIT WHEN substring_loc = 0; -- So far so good. Let's raise the return value and try again. return_value := return_value + 1; END LOOP; RETURN return_value - 1; END freq_instr2; /