MySQL case-insensitive DISTINCT

Raymond Ativie picture Raymond Ativie · Jul 25, 2013 · Viewed 14.6k times · Source

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

[email protected]
[email protected]
[email protected]
[email protected]

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 [email protected] and [email protected] just because they are different cases?

Answer

Robert picture Robert · Jul 25, 2013

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.