Oracle : REGEXP_REPLACE in a substring

Question

I need to replace some of characters from email addresses. For example :

If my email address is : patty.beads@mydomain.com , the output should be :

pattybeads@mydomain.com <-- removed '.' before '@mydomain.com'

Again if my email address is patty.beads+something@mydomain.com, the output should be :

patty.beads+something@mydomain.com <-- removed '.' and '+' before '@mydomain.com'

Hence, expecting some of the chars ( like '.','+' etc ) before @mydomain.com should be removed. While I am seeing that I can use REGEXP_REPLACE to replace those chars, getting hard time to understand how to include the constraint that the replacement has to be in the substring before '@'.

May I get any help?

Answer 1

You may split the domain and name and then apply REGEXP_REPLACE or TRANSLATE on name.

Another option is to use INSTR and SUBSTR

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table yourtable as
select 'pattybeads@mydomain.com' emailid FROM DUAL UNION ALL
select 'patty.beads@mydomain.com' FROM DUAL UNION ALL
select 'patty.beads+something@mydomain.com' FROM DUAL

Query 1:

select TRANSLATE (name, 'a.+', 'a' ) ||domain emailid
FROM
( SELECT 
    REGEXP_SUBSTR ( emailid, '(.+@)(.+)' ,1,1,NULL,1) name,
    REGEXP_SUBSTR ( emailid, '(.+)@(.+)' ,1,1,NULL,2) domain 
FROM yourtable
 )

Results:

|                          EMAILID |
|----------------------------------|
|          pattybeads@mydomain.com |
|          pattybeads@mydomain.com |
| pattybeadssomething@mydomain.com |

Query 2:

 select TRANSLATE ( SUBSTR (emailid,1,INSTR( emailid,'@' ) -1 ) ,
          'a.+', 'a' )||
         SUBSTR (emailid,INSTR( emailid,'@'  ) ) emailid
 FROM yourtable

Results:

|                          EMAILID |
|----------------------------------|
|          pattybeads@mydomain.com |
|          pattybeads@mydomain.com |
| pattybeadssomething@mydomain.com |