CODEWITHSUNDEEP
sqloracle-databasereplaceoracle-sqldeveloper
user2217874
user2217874

Reputation: 141

Replace multiple characters with a single line in ORACLE SQL

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.

Upvotes: 0

Views: 209

Answers (1)

pablomatico
pablomatico

Reputation: 2242

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.

Upvotes: 1

Related Questions