7-13. Finding a Pattern Within a String

Problem

You want to find the number of occurrences of a particular pattern within a given string. For instance, you want to search for email addresses within a body of text.

Solution

Use a regular expression to match a given string against the body of text and return the resulting count of matching occurrences. The following example searches through a given body of text and counts the number of email addresses it encounters. Any email address will be added to the tally because a regular expression is used to compare the strings.

CREATE OR REPLACE PROCEDURE COUNT_EMAIL_IN_TEXT(text_var     IN VARCHAR2) AS   counter    NUMBER := 0;   mail_pattern    VARCHAR2(15) := '\w+@\w+(\.\w+)+'; BEGIN   counter := REGEXP_COUNT(text_var, ...

Get Oracle and PL/SQL Recipes: A Problem-Solution Approach now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.