Tip of the Week
Tip for Week of November 27, 2006

Count Repeated Strings

This tip comes from Jose Luis Messina, a Programmer/Analyst at La Segunda SA, in Santa Fe, Argentina.

create or replace function f_CountChr(p_instring VARCHAR2,
p_inpattern VARCHAR2) return NUMBER is


/*|||||||||||||||||||||||||||||||||||||||||||||||||
This process returns the times that the first parameter "p_inpattern" appears
in the second parameter "p_instring"

For example:
Example 1
-SELECT f_CountChr('ASASDSDS','ASA') FROM dual
- Return 1

Example 2
-SELECT f_CountChr('Radiation found on 2 jets in spy probe','on') FROM dual
- Return 2

Author: Jose Luis Messina
|||||||||||||||||||||||||||||||||||||||||||||||||*/

v_numChr NUMBER;

begin
v_numchr:= (length(p_instring)-length(REPLACE(p_instring,p_inpattern,'')))
/length(p_inpattern);
return(v_numchr);
end f_CountChr;