Extracting original email and remove email alias in emails

I’m trying to get the primary email from aliases, for example, I have the following examples and I need to normalize them.

1. from “[e-mail]” to “[e-mail]”
2. from “[e-mail]” to “[e-mail]”
3. from “[e-mail]” to “[e-mail]”
4. from “[e-mail]” to “[e-mail]”

I wrote a SQL statement on bigquery but got an error that says “Third argument in SUBSTR() cannot be negative”

SELECT  TRANSLATE(SUBSTR('[e-mail]', 1, INSTR('[e-mail]', '+')-1),'+.', '') ||'@' || SUBSTR('[e-mail]', Instr('[e-mail]','@')+1)

Any ideas or suggestions for the above or ideas for useing regex_replace instead

Thanks

Answer

Consider below approach

select email, 
  regexp_replace(email, r'([^+@]+)+?([^@]*(.*))', r'13') original_email
from your_table             

if applied to sample data in your question – output is

In case if for some reason you want dots to be removed – use below

select email, original_email,
  format('%s@%s', 
    replace(split(original_email, '@')[offset(0)], '.', ''), 
    split(original_email, '@')[offset(1)]
  ) with_stripped_dots
from your_table, 
unnest([regexp_replace(email, r'([^+@]+)+?([^@]*(.*))', r'13')]) original_email             

with output