This content has been marked as final. Show 3 replies
If you can easily list all the characters you want removed, then using TRANSLATE is simple and efficient:
The 2nd argument to TRANSLATE starts with one of the characters that you want to keep, followed by all the characters you want to remove.
SELECT TRANSLATE ( phone_number , '0 ()-' , '0' ) AS digits_only FROM interest ;
The 3rd argument is just the 1st character from the 2nd argument ('0' in this example). It doesn't matter if this character actually occurs in the 1st argument or not.
If you want to keep only the digits, and remove all the other characters, whatever they might be, and you don't know what they might be, then TRANSLATE is still the fastest way, but it's more complicated. REGEXP_REPLACE is much simpler:
SELECT REGEXP_REPLACE ( phone_number , '\D' ) AS digits_only FROM interest ;
1* select translate('(719) 696-5689','1234567890 -()','1234567890') from dual SQL> / TRANSLATE( ---------- 7196965689
Works like a charm!