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:

http://apex.oracle.com/pls/apex/f?p=31517:282

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


on

6 comments:

  1. Alex Nuijten16 February 2013 at 13:53

    Why not use built-in functionality?

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

    ReplyDelete
  2. Alex Nuijten16 February 2013 at 13:55

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

    ReplyDelete
  3. Denes Kubicek16 February 2013 at 13:59

    Alex,

    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.

    Denes

    ReplyDelete
  4. Alex Nuijten16 February 2013 at 14:28

    Denes,
    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

    Alex

    ReplyDelete
  5. Rob van Wijk17 February 2013 at 13:11

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

    ( length(p_string) -
    length(replace(p_string,p_substring)
    ) / length(p_substring)

    Regards,
    Rob.

    ReplyDelete
  6. Denes Kubicek17 February 2013 at 14:33

    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.

    Thanks,

    Denes

    ReplyDelete