Skip to content

Regex to match duplicate/alias e-mails in MySQL

I am trying to come up with some regular expression to check whether an e-mail exists in a database. What is more specific here is that I want to find e-mails that are the same, but might be written differently. E.g. is the same as and

Using another script I remove all dots and text after + so that becomes How can I use this “stripped” address to match its aliases in a MySQL database?

I have little experience with RegEx and I haven’t really used it alongside SQL before. Anyway, I could only come up with the following code:

RegEx (matches all +<any text> and . before the @ symbol



SELECT email FROM users WHERE email REGEXP '(+.*(?=@)|.(?=.*@))'
SELECT * from users WHERE email REGEXP_LIKE('', '(+.*(?=@)|.(?=.*@))')

I get the following error for both:

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near REGEXP_LIKE('', '(+.*(?=@)|.(?=.*@))')

What I am trying to do is select email from users where email = '' but disregard these characters (+.*(?=@)|.(?=.*@)
(the code is executed as a prepared statement)

Any tip or advice would be highly appreciated!



In MySQL, the simplest method might be breaking the problem into the username and domain:

where replace(substring_index(substring_index(email, '@', 1), '+', 1), '.', '') = substring_index('', '@', 1) and
      substring_index(email, '@', -1) = substring_index('', '@', -1) and
      email like '%@%' and
      email not like '%@%@%'
4 People found this is helpful