CODEWITHSUNDEEP
oracle-databaseplsql
Mark Harrison
Mark Harrison

Reputation: 304544

Oracle: counting the number of substrings in a string?

How do I count the number of occurrences of a substring inside of a string?

Upvotes: 4

Views: 6972

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

In pre-11g version you can:

select (length(string) - length(replace(string,substring,''))) / length(substring) as occ
from dual;

The idea is to see how much space in the string is occupied by the substring, then, to see how many times the substring is in that space just divide to the length of substring.

Upvotes: 2

Mark Harrison
Mark Harrison

Reputation: 304544

As of version 11g, regexp_count will do this.

select regexp_count('abba', 'b') from dual;
   2 
select regexp_count('abba', 'b+') from dual;
   1

Upvotes: 6

Related Questions