Saturday, 16 February 2013

Count Substring Occurrences in a String

If you write a lot of PL/SQL code, sooner or later you will be faced with a requirement to count the number of occurrences of a substring in a string. In that case you may use this example to help yourself:

It will search not only for a single character withing a string but also for a substring of any length up to 4000 characters.


Alex Nuijten said...

Why not use built-in functionality?

regexp_count('How manyline breaks arein thisstring?', '')

Alex Nuijten said...

and of course the comment box remove the </br> tags...

Denes Kubicek said...


1. it is easy if you know that function exists (I didn't think of regular expressions ;))

2. it is always good to know how to do it yourself :)

3. "and of course the comment box remove the tags..." is something I don't understand.


Alex Nuijten said...

1. that does make it easier, yes :)
2. sure
3. I entered
regexp_count('How many</br>line breaks</br> are</br>in thisstring?', '</br>')
but the comment box removed the HTML tags... no matter


Rob van Wijk said...

And if you're not on Oracle11 yet, when regexp_count was introduced, you can still use this:

( length(p_string) -
) / length(p_substring)


Denes Kubicek said...

Sure, a cool way to find it out. Actually, the regexp function seems to be limited to 512 characters for the second parameter. This is a better way.