4

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

Mark Harrison
  • 297,451
  • 125
  • 333
  • 465
  • Closely related, although not quite a duplicate: [Counting the number of occurrences of a character in Oracle SQL](http://stackoverflow.com/q/10772091/119527) – Jonathon Reinhart Dec 18 '14 at 21:09
  • Also, I found this: http://stackoverflow.com/questions/8169471/how-to-count-number-of-occurences-of-a-character-in-an-oracle-varchar-value – DaaaahWhoosh Dec 18 '14 at 21:17

2 Answers2

6

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
Mark Harrison
  • 297,451
  • 125
  • 333
  • 465
  • 1
    Am I going crazy, or did you just answer your own question? – DaaaahWhoosh Dec 18 '14 at 21:04
  • Looks like a pitch at the selfie hat – Matt Coubrough Dec 18 '14 at 21:05
  • 1
    @DaaaahWhoosh This is perfectly acceptable behavior: http://meta.stackexchange.com/a/189497/229922 – Sylvain Leroux Dec 18 '14 at 21:07
  • 6
    Hey guys, consider reviewing the Stack Overflow guidelines before down-voting: [**Can I answer my own question?**](http://stackoverflow.com/help/self-answer) *Yes! Stack Exchange has always explicitly encouraged users to answer their own questions. If you have a question that you already know the answer to, and you would like to document that knowledge in public so that others (including yourself) can find it later, it's perfectly okay to ask and answer your own question on a Stack Exchange site.* – Jonathon Reinhart Dec 18 '14 at 21:07
  • @Jonathon Reinhart - Yes and it seems its not a duplicate, and its a clean approach – Matt Coubrough Dec 18 '14 at 21:08
  • That being said, this is a very good occasion as well for other answerers to provide alternative solutions. – Sylvain Leroux Dec 18 '14 at 21:08
  • @DaaaahWhoosh, yes I did... I use SO as my programmer's notebook, so that other people will be able to take advantage of something I've had to figure out for myself. Plus, after I've forgotten all about it, google will bring me back to my original question! – Mark Harrison Mar 27 '15 at 08:45
2

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.

Florin Ghita
  • 17,525
  • 6
  • 57
  • 76