MySQL case-insensitive DISTINCT

Question

Can anyone tell me how i can SELECT DISTINCT from my database without it being case-sensitive?

My query is

SELECT DISTINCT email FROM `jm_order`

The results brings out all the emails in the table but repeats the ones with different cases. This is expected because the values are different case wise. e.g

sam@gmail.com
josh@gmail.com
Sam@gmail.com
john@gmail.com

But what i want is for the same emails, to be grouped together regardless of the case. What adjustment can i make to my SQL to stop it from repeating for example sam@gmail.com and Sam@gmail.com just because they are different cases?

Possible duplicate of ["SELECT DISTINCT" ignores different cases](https://stackoverflow.com/questions/2644851/select-distinct-ignores-different-cases) — Cees Timmerman, Oct 11 '17 at 08:05

score 23 · Accepted Answer · answered Jul 25 '13 at 14:18

23

Try to use upper function

SELECT DISTINCT UPPER(email) FROM `jm_order`

you can also use lower instead

SELECT DISTINCT LOWER(email) FROM `jm_order`

More information.

answered Jul 25 '13 at 14:18

Robert

25,425
8
67
81

score 9 · Answer 2 · answered Jul 25 '13 at 14:22

9

If you want to preserve the case of the email (so it actually matches one of the rows), you can do:

select email
from jm_order
group by lower(email);

answered Jul 25 '13 at 14:22

Gordon Linoff

1,242,037
58
646
786

Does this really work? How it this even possible? How would the DBMS know which result it should return. – T3rm1 Jun 06 '19 at 13:23
@T3rm1 . . . MySQL returns an email from an arbitrary matching row. In more recent versions of MySQL, this would actually generate an error using the default settings. – Gordon Linoff Jun 06 '19 at 14:25

score 2 · Answer 3 · answered Jul 25 '13 at 14:20

2

Try this:

SELECT DISTINCT LOWER(email) AS email 
FROM `jm_order`

answered Jul 25 '13 at 14:20

Dave Sexton

10,768
3
42
56

MySQL case-insensitive DISTINCT

3 Answers3