CODEWITHSUNDEEP
mysqlsqldatabasedistinctcase-insensitive
Raymond Ativie
Raymond Ativie

Reputation: 1826

MySQL case-insensitive DISTINCT

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?

Upvotes: 17

Views: 20364

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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);

Upvotes: 9

Dave Sexton
Dave Sexton

Reputation: 11188

Try this:

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

Upvotes: 2

Robert
Robert

Reputation: 25753

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.

Upvotes: 23

Related Questions