Search code examples
mysqlsqlstringselectemail-address

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

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

email
----------------------
[email protected]
[email protected]
[email protected]

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

email
----------------------
[email protected]
[email protected]
[email protected]
Solution

  • 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.