How to find duplicate email in mysql table

Question

I want to get a duplicate letter from the table:

userid email ------------------------- 1 [e-mail] 2 [e-mail] 3 abc%40gmail.com 4 [e-mail] 5 abcd%40gmail.com 

So, from the above records I want to get the result, for example

 Email Count ------------------------- [e-mail] 2 [e-mail] 2 [e-mail] 1 

Does anyone know how to do this?

Thanks.

Answer 1

You cannot directly do this in MySQL, because there is no function for urlencode or urldecode .

You will need to create a user-defined function to handle this process. Once you have this feature, just go to a simple group by with a having .

Link to required UDFs

If UDFs are not an option, the only workaround I can think of is to manually replace characters (at your own risk):

 SELECT REPLACE(email, "%40", "@") DuplicateEmail, COUNT(*) Amount FROM t GROUP BY DuplicateEmail ORDER BY Amount desc 

Fiddle is here .

Output:

 | DUPLICATEEMAIL | AMOUNT | --------------------------- | [e-mail] | 2 | | [e-mail] | 2 | | [e-mail] | 1 | 

Answer 2

If you want to output the data exactly as shown in your question, use this query:

 SELECT email, COUNT(*) AS count FROM table GROUP BY email HAVING count > 0 ORDER BY count DESC;