/*
This function will return the number of occurences that p_sub_string appears in p_string
Unlike instr that only counts the position of a sub-string in a string.
*/
CREATE OR REPLACE FUNCTION occurs
(p_string IN VARCHAR2,
p_sub_string IN VARCHAR2)
RETURN NUMBER
AS
BEGIN
RETURN (LENGTH (p_string) – LENGTH (REPLACE (p_string, p_sub_string, ”))) / LENGTH (p_sub_string);
END occurs;
How to count the number of occurences in a string