devxlogo

Count the Number of Occurrences of a String Within a String

Count the Number of Occurrences of a String Within a String

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.

devx-admin

Share the Post: