1. Worksheets

The purpose of this weeks worksheet is to provide additional practice. Please attempt the worksheet without using provided answers. 

2. Questions and Completion 

To receive credit for completing the worksheet, you must say ‘Done’ on the worksheet folder. 

If you have questions as you go through this worksheet, please feel free to email the instructor. 

3. Simple PL/SQL Function

Create a function called format_phone. It will format the following string 123.456.7890 so that it looks like a U.S. phone number (123) 456-7890.

Note 1:  Add two variables of type VARCHAR2 and length 50. The first variable will hold an unformatted phone number and the second a formatted phone number. Initialize the first variable to 123.456.7890.

Note 2: Look up  PL/SQL functions replace and substr.

4. Parametric Function

Create a table Employee with last_name varchar2(50), first_name varchar2(50), phone_number varchar(13). Next insert some sample data of form (‘Joe’, ‘Smith’,’123.456.7890’) and (‘Chris’, ‘White’, ‘324.675.2344’)

Modify the function to use an input parameter instead of a hard coded variable value. Compile and test the function using '123.456.7890' as the input parameter. In this function map the variables created in Exercise #3 to variable of type Employee phone number. Finally use the function to format all phone numbers in Employee to a U.S. phone number format. 

5. Regex Function

Replace the function created above with one that uses regular expressions.

Look into regexp-instr and regexp_replace. 

6. Check answers

Answers are here, but look at these answers after you have tried the questions.