Identify duplicate email with . inside it

Question

How to identify duplicate emails in table with . inside in ruby on rails

E.x . user 1: testaccount@gmail.com,

  user 2: test.account@gmail.com,

  user 3: tes.t.account@gmail.com,

  user 4: test.a.ccount@gmail.com

Gmail refers to same email account for each emails included by '.' . Gmail ignores dots in email username

Normalise the field `before_validation` by stripping the dots from the left side in a unique column? — Amadan, Feb 03 '16 at 07:13
An email with a dot does not indicate it will be a duplicate. Relevant: http://stackoverflow.com/questions/2049502/what-characters-are-allowed-in-email-address — Josh Brody, Feb 03 '16 at 07:20
@JoshBrody: it is creating malicious accounts in any other site. — Debadatt, Feb 03 '16 at 07:22
I don't think an email address is going to stop someone from being malicious. — Josh Brody, Feb 03 '16 at 07:24
Why don't you just make them verify their email address by emailing them a link to click. Then add a cleanup job that deletes any unverified accounts after 7 days or something. — Jon, Feb 03 '16 at 07:54

score 1 · Answer 1 · answered Feb 03 '16 at 07:25

in postgesql:

select distinct a from (select replace(adr,'.','') as a from t) t2;

gives you unique set

select a,count(*) from (select replace(adr,'.','') as a from t) t2 group by a;

gives you how many times each value has been met

score 0 · Answer 2 · answered Feb 03 '16 at 11:25

To-the-dot duplicated email records can be identified directly in your SQL statement or in your ruby app code.

Here's a simple query to return all normalized email and the number of users associated with each normalized email:

User.group("replace(email,'.','')").count

which is translated to the following SQL:

SELECT COUNT(*) AS count_all, replace(email,'.','') AS replace_email FROM "users" GROUP BY replace(email,'.','')

and returns something like the following hash:

{"x@gmailcom"=>1, "da@gmailcom"=>2}

Indicating there are 2 users with normalized email equals to da@gmailcom.

Alternitevly you can use group_by in ruby code:

User.all.group_by{ |u| u.email.gsub('.','') }

2 Answers2