Regex in Oracle PL/SQL to remove unwanted characters from a string containing a phone number

Question

I need to remove the characters -, +, (, ), and space from a string in Oracle. The other characters in the string will all be numbers. The function that can do this is REGEXP_REPLACE. I need help writing the correct regex.

Examples: string '23+(67 -90' should return '236790' string '123456' should return '123456'

Answer 1

Something like

SQL> ed
Wrote file afiedt.buf

  1  with data as (
  2    select 'abc123def456' str from dual union all
  3    select  '23+(67 -90' from dual union all
  4    select '123456' from dual
  5  )
  6  select str,
  7         regexp_replace( str, '[^[:digit:]]', null ) just_numbers
  8*   from data
SQL> /

STR          JUST_NUMBERS
------------ --------------------
abc123def456 123456
23+(67 -90   236790
123456       123456

should do it. This will remove any non-digit character from the string.

Answer 2

regexp_replace is an amazing function, but it is a bit difficult.

You can use TRANSLATE function to replace multiple characters within a string. The way TRANSLATE function differs from REPLACE is that, TRANSLATE function provides single character one to one substitution while REPLACE allows you to replace one string with another.

Example:

SELECT TRANSLATE('23+(67 -90', '1-+() ', '1') "Replaced" FROM DUAL;

Output:

236790

In this example, ‘1’ will be replaced with the ‘1’ and ‘-+()‘ will be replaced with null value since we are not providing any corresponding character for it in the ‘to string’. This statement also answers your question without the use of regexp.

You would think that you could use empty string as the last argument, but that doesn't work because when we pass NULL argument to TRANSLATE function, it returns null and hence we don’t get the desired result.

So I use REPLACE if I need to replace one character, but TRANSLATE if I want to replace multiple characters.

Source: https://decipherinfosys.wordpress.com/2007/11/27/removing-un-wanted-text-from-strings-in-oracle/