The select statement below takes the name “george” and finds how many occurrences of “ge” appear within it.
select (length('george') - length (replace('george','ge',''))) / length('ge') countfrom dual/ COUNT---------- 2
Using this information, you could write a function like the one below:
create function countStr(document varchar2,search varchar2) return number is counter number; begin select (length('george') - length (replace('george','ge',''))) / length('ge') count into counter from dual; return counter; end;SQL> /Function created.SQL> select countStr('george','ge') from dual;COUNTSTR('GEORGE','GE')----------------------- 21 row selected.