How do I remove 'plus' tags from email addresses in a MySQL query?

Question

I have a table containing email addresses where some of them contain 'plus' tags - for example,

email
----------------------
user1@example.com
user2@example.org
user1+test@example.com

I need to be able to query the table and receive the email addresses without the tags:

email
----------------------
user1@example.com
user2@example.org
user1@example.com

Answer 1

This query strips the tag from email addresses on all versions of MySQL:

SELECT IF(LOCATE('+', email) = 0, 
  email,
  CONCAT(
    SUBSTRING(email, 1, LOCATE('+', email) - 1), 
    SUBSTRING(email, LOCATE('@', email))))
FROM my_table; 

In English: if there is no + in the email address, then just use the email address; otherwise, concatenate the text to the left of the + with the text from the @ to the end of the string.

Answer 2

If you are running MySQL 8.0, you can use regexp_replace() for this:

select
    email,
    regexp_replace(email, '\\+[^@].*@+', '@') new_email
from mytable

This phrases as: suppress any sequence of characters starting with '+' until '@'. If there is no match, regexp_replace() leaves the string untouched.

Demo on DB Fiddle:

with mytable as (
    select 'user1@example.org' email
    union all select 'user2@example.org'
    union all select 'user1+test@example.com'
)
select
    email,
    regexp_replace(email, '\\+[^@].*@+', '@') new_email
from mytable;

| email                  | new_email         |
| ---------------------- | ----------------- |
| user1@example.org      | user1@example.org |
| user2@example.org      | user2@example.org |
| user1+test@example.com | user1@example.com |