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?

Kris Rice · Answer 1 · 2018-02-09T17:46:21.643

2

What you are looking for , I think, is this How to match "anything up until this sequence of characters" in a regular expression?

Which Oracle doesn't implement in the db ( as far as I can tell )

So that means splicing it apart based on the @ , removing the .|+ from the first part, then concatenating back together.

SQL> with q as ( select 'patty.beads+something@mydomain.com' email from dual ) 
    select 
          REGEXP_REPLACE(substr(q.email,0,instr(q.email,'@')-1),'\.|\+','',1,0) 
          ||
          substr(q.email,instr(q.email,'@')) new_email
        from q;

NEW_EMAIL                       
--------------------------------
pattybeadssomething@mydomain.com

edited Feb 09 '18 at 17:46

answered Feb 09 '18 at 17:20

Kris Rice

3,300
15
33

Should have said the full doc for regex_replace is here for more robust explanations of the parameters : https://docs.oracle.com/database/121/SQLRF/functions163.htm#SQLRF06302 – Kris Rice Feb 09 '18 at 17:23
but this replaces the '.' from @mydomain.com as well and that is not expected. result should be pattybeadssomething@mydomain.com (those characters should be replaced before the part @mydomain.com) – VictorGram Feb 09 '18 at 17:26

Kaushik Nayak · Accepted Answer · 2018-02-09T17:49:30.423

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 |

score 1 · Answer 3 · answered Feb 09 '18 at 18:49

If I understood you correctly, you want to leave only letters (possibly digits?) in the first part of the e-mail address. If that's so, how about this?

In REGEXP_REPLACE, the \W represents a non-word, so - anything that isn't a letter or a digit or an underscore (as it is considered to be a word) is being removed. In order to remove the underscore as well, |_ is added so - finally - it is read as remove non-words or underlines.

SQL> with test (email) as (
  2    select 'pattybeads@mydomain.com'            from dual union all
  3    select 'patty.beads@mydomain.com'           from dual union all
  4    select 'patty.beads+something@mydomain.com' from dual union all
  5    select 'patty-beads$something@mydomain.com' from dual union all
  6    select 'pat_ty#b.e?a!d*s@mydomain.com'       from dual
  7  )
  8  select email,
  9    regexp_replace(substr(email, 1, instr(email, '@')), '\W|_', '') ||
 10    substr(email, instr(email, '@')) result
 11  from test;

EMAIL                              RESULT
---------------------------------- ----------------------------------------
pattybeads@mydomain.com            pattybeads@mydomain.com
patty.beads@mydomain.com           pattybeads@mydomain.com
patty.beads+something@mydomain.com pattybeadssomething@mydomain.com
patty-beads$something@mydomain.com pattybeadssomething@mydomain.com
pat_ty#b.e?a!d*s@mydomain.com      pattybeads@mydomain.com

SQL>

Oracle : REGEXP_REPLACE in a substring

3 Answers3