Clever Geek Handbook

Oracle: counting the number of substrings in a string?

How to count the number of occurrences of a substring within a string?

+3


source to share


2 answers


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
+4


source


Before 11g, you can:

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


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

+1


source






More articles:


All Articles