ACCESS SQL – Is there a way to select only a specific part of a field using Wild Cards?

I have a table I am trying to clean up. There is a Table called PERSONS. In the PhoneNumber column, it sometimes includes data such as "(Work)" or "(Home)". It is inconsistent and may include it before or after the number such as "(Home) 111-555-3354" or "111-555-2345 (Home)".

Is there a way of extracting only the phone number portion of each field such as in conjunction with "LIKE ###-###-####".

I then should be able to insert and replace the values with this select clause.

>Solution :

You can do it using Mid function in MS Access as below :

Mid function returns a Variant (String) containing a specified number
of characters from a string.

SELECT Mid(PhoneNumber, InStr(PhoneNumber, "-") - 3, 12) AS CleanedPhoneNumber
FROM Persons
WHERE PhoneNumber Like "*###-###-####*";

For the following PhoneNumber;

(Home) 111-555-3354
111-555-2345 (Home)
112-524-5632 (Work)
112-652-6354 (Work)

The output would be :

111-555-3354
111-555-2345
112-524-5632
112-652-6354