下面的函数统计substring_in在string_in里出现的次数:/* Filename on companion disk: freqinst.sf */
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. */
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; /* Did I find another occurrence? */
check_again := search_loc > 0;
IF check_again
THEN
return_value := return_value + 1; /* Move start position past the substring. */
search_loc := search_loc + substring_len;
END IF;
END LOOP;
END IF; RETURN return_value;END freq_instr1;
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. */
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; /* Did I find another occurrence? */
check_again := search_loc > 0;
IF check_again
THEN
return_value := return_value + 1; /* Move start position past the substring. */
search_loc := search_loc + substring_len;
END IF;
END LOOP;
END IF; RETURN return_value;END freq_instr1;
创建这个函数后,可以通过如下的语句得到结果:select id,sql_char,freq_instr1(sql_char,'$') $_count,
mod(freq_instr1(sql_char,'$'),2) odd_or_even from tablename ;