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.