How to find duplicate email within a mysql table

Question

I want to fetch duplicate email from table:

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

So from above records i want result like

Email          Count
-------------------------
[email protected]   2
[email protected]  2
[email protected]   1

Does anybody know how to manage that?

Thanks.

Scorpaenoid

Answer 1

You can't directly do that in MySQL because there is no function to urlencode or urldecode strings.

You will have to create a User Defined Function to handle that process. Once you have that function just go for a simple group by with a having clause.

Link to the required UDFs

If UDFs are not an option, the only workaround I can think of is manually replacing the chars (under your own risk):

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

Fiddle here.

Output:

| DUPLICATEEMAIL | AMOUNT |
---------------------------
|  [email protected] |      2 |
| [email protected] |      2 |
|  [email protected] |      1 |

Actiniform

No i want to fetch both records that contains @ & %40 – Scorpaenoid

That is not what you have in your example... you are only displaying the ones with the @ in your result. The output of my query is what you are looking for in your expected result. Please, update your question to display the output you are now saying you need. – Actiniform

Answer 2

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

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

Edora

Just a friendly note for whoever flagged this: while this is not the CORRECT answer, it is AN answer - that's what downvotes are for. Flagging "not an answer" is for when someone adds a comment (e.g. "me too") as an "answer". Cheers! – Mathison

The above by @Edora is how I de-dupe tables. I will sometimes LOWER(email) incase someone used upper & lower case when entering their address. I also only show the duplicate records with count > 1. Works well. – Irreplaceable

Answer 3

Here is a simple solution:

SELECT email, COUNT(1) FROM table_name GROUP BY email HAVING COUNT(1) > 1

Plyler

Welcome to Stack Overflow. If you decide to answer an older question that has well established and correct answers, adding a new answer late in the day may not get you any credit. If you have some distinctive new information, or you're convinced the other answers are all wrong, by all means add a new answer, but 'yet another answer' giving the same basic information a long time after the question was asked usually won't earn you much credit. What you've provided is equivalent to an existing answer, and doesn't deal with the URL encoding of the data. It isn't helpful, therefore. – Leveret