I have a phone number and zip code field in a table. I am trying to get this information into a common format, and I want to get rid of all the extra junk like dashes, parenthesis, spaces, and letters.
I was wondering if there was a way to do this with the replace function, I tried doing it similarly to how one would in REGEXP_LIKE() and had no luck, this is what I have.
select (REPLACE(numbers.PHONE,'[a-zA-Z._-%() ]',''))
from table numbers;
If there isn't a way to do this that's fine, I just wanted to avoid having to make a whole bunch of replace statements for everything I want to replace.
It would depend on how much junk you have in your zip codes and phones. For example, you could remove all non-digital characters in those fields with a replace like this one:
SELECT REGEXP_REPLACE('234N2&.-@3NDJ23842','[^[:digit:]]+') FROM DUAL
And afterwards you could format the resulting digits with a replace like this:
SELECT REGEXP_REPLACE('2342323842','([[:digit:]]{3})([[:digit:]]{3})([[:digit:]]{4})','\1 \2 \3') FROM DUAL
I know the examples are not valid as zip codes nor phone numbers but I think they might help you.