mysqlduplicate-data

How to find duplicate email within a mysql table

I want to fetch duplicate email from table:

userid      email
-------------------------
1       abc@gmail.com
2       abcd@gmail.com
3       abc%40gmail.com
4       xyz@gmail.com
5       abcd%40gmail.com

So from above records i want result like

Email          Count
-------------------------
abc@gmail.com   2
abcd@gmail.com  2
xyz@gmail.com   1

Does anybody know how to manage that?

Thanks.

Solution

  • 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 |
---------------------------
|  abc@gmail.com |      2 |
| abcd@gmail.com |      2 |
|  xyz@gmail.com |      1 |