Square DBA – Oracle DBA Talk

Clean up phone numbers

I needed to clean up a list of phone numbers today. The phone numbers where in a varchar2 column in the database and were listed like this: (916) 555 -1212.

I knew I could used some instr and substr functions to cut the data out, but I wanted to use some of the regex functions found in Oracle 10G and above. This handy little piece of code simply removed all non numeric digits from a string.

select REGEXP_REPLACE(CALLNUMBER,'[^[:digit:]]', NULL)
from temp_cima where rownum < 11;