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; ...Get Oracle and PL/SQL Recipes: A Problem-Solution Approach now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
