How to find duplicate emails due to case sensitivity-CodePudding

I am trying to find the IDs that have duplicate emails due to case sensitivity.

For below table

ID      Email
101     [email protected]
101     [email protected]
102     [email protected]
102     [email protected]

The output should give below

ID      Email 
101     [email protected]
101     [email protected]

Only ID 101 and its email addresses should be displayed because that's the only ID that has duplicate emails due to case sensitivity.

Thank you in advance!

CodePudding user response：

Use a self-join that performs both case-sensitive and case-insensitive comparisons.

SELECT a.id, a.email
FROM yourTable AS a
JOIN yourTable AS b 
    ON a.id = b.id 
    AND a.email = b.email COLLATE utf8mb4_general_ci 
    AND a.email != b.email COLLATE utf8mb4_bin

CodePudding user response：

You can do:

select * 
from t
where lower(email) in (
  select lower(email)
  from t
  group by lower(email)
  having min(email) <> max(email)
)