MySQL case-insensitive DISTINCT

mysql sql database distinct case-insensitive
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.

Related questions

What's faster, SELECT DISTINCT or GROUP BY in MySQL?

If I have a table CREATE TABLE users ( id int(10) unsigned NOT NULL auto_increment, name varchar(255) NOT NULL, profession varchar(255) NOT NULL, employer varchar(255) NOT NULL, PRIMARY KEY (id) ) and I want to get all unique values of profession …

Read More
Count the occurrences of DISTINCT values

I am trying to find a MySQL query that will find DISTINCT values in a particular field, count the number of occurrences of that value and then order the results by the count. example db id name ----- ------ 1 Mark 2 …

Read More
Selecting distinct 2 columns combination in mysql

I have a mysql table that looks like this: 1 value1 value2 3534 2 value1 value1 8456 3 value1 value2 3566 4 value1 value3 7345 5 value2 value3 6734 I need a query to select all the rows with distinct column 2 and 3, for example the output I want for this …

Read More