REGEXP to identify and replace an email

long story short, I need to identify an email that may have 2 different endings:

“[e-mail]” “[e-mail]”

I need to replace instances of the two above examples with:

“[e-mail]”

I am mostly new to Oracle, here’s what I have so far:

CASE
    WHEN REGEXP_LIKE(EMAIL_LIST, '^[e-mail]$', 'i') THEN REGEXP_REPLACE(EMAIL_LIST,'^[e-mail]$', ';[e-mail];')
    WHEN NOT REGEXP_LIKE(EMAIL_LIST, '^[e-mail]$', 'i') THEN EMAIL_LIST || ';[e-mail];'
    ELSE EMAIL_LIST
END

The email_list column that holds the emails contains emails separated by ‘;’. For instance: '[e-mail];[e-mail];[e-mail];[e-mail];' is one row from that column. I want to change that so the above row would look like this: [e-mail];[e-mail];[e-mail].

It’s pretty easy to just check for one of the emails, and I am aware I can just put in an OR… and add in another REGEXP_LIKE statement with the other email, but is there a way I can look for both emails in one REGEXP_LIKE statement?

Thanks

Answer

Yes, there is. Make the ‘abc’ optional:

HREmail@company(abc)?.com$

Don’t forget to escape the dot in regular expressions!

Question: Do you need to replace both with [e-mail]? That is not what you have in your code. In your code, in the first case it’s [e-mail].

If you must replace both with [e-mail], it’s trivial. For the more interesting case:

regexp_replace(<string>, '^HR(Email@company(abc)?.com)$', 'IT1')

This will replace HR with IT and then copy the first capturing group (what’s within the outer parentheses) exactly as it is in the input – with or without abc.

For example:

with
  sample_inputs(str) as (
    select '[e-mail]'       from dual union all
    select '[e-mail]'    from dual union all
    select '[e-mail]'    from dual union all
    select '[e-mail]'      from dual union all
    select '[e-mail]' from dual union all
    select '[e-mail]'    from dual
  )
select str as old_str, 
       regexp_replace(str, '^HR(Email@company(abc)?.com)$', 'IT1')
         as new_str
from   sample_inputs
;

OLD_STR                  NEW_STR                 
------------------------ ------------------------
[e-mail]         [e-mail]        
[e-mail]      [e-mail]     
[e-mail]      [e-mail]     
[e-mail]        [e-mail]       
[e-mail]   [e-mail]  
[e-mail]      [e-mail]  

EDIT (one of many) – I saw later in your question that you need to replace in a semicolon-separated list, not the full string. So, why did you have the ^ and $ anchors in your attempt?

Anyway – you can adapt what I gave you above to a list like you have. Write back if you need help with that part (which is quite unrelated to the main question, of how to handle both “to-be-replaced” strings in a single regexp).