Oracle and PL/SQL Recipes: A Problem-Solution Approach

7-14. Determining the Position of a Pattern Within a String

Problem

You want to return the position of a matching string within a body of text. Furthermore, you are want to pattern match and therefore must invoke a regular expression function. For example, you need to find a way to determine the position of a string that matches the pattern of a phone number.

Solution

Use the REGEXP_INSTR function to use a regular expression to search a body of text to find the position of a phone number. The following code block demonstrates this technique by looping through each of the rows in the EMPLOYEES table and determining whether the employee phone number is USA or international:

DECLARE   CURSOR emp_cur IS   SELECT *   FROM employees;   emp_rec       emp_cur%ROWTYPE; ...