MySQL case-insensitive DISTINCT?

If you want case-insensitive distinct, you need to use UPPER() or LOWER().

Case 1: Using UPPER().

The syntax is as follows:

SELECT DISTINCT UPPER(yourColumnName) FROM yourTableName;

Case 2: Using LOWER().

The syntax is as follows:

SELECT DISTINCT LOWER(yourColumnName) FROM yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table CaseInsensitiveDistinctDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> UserEmailId varchar(30),
   -> UserPassword varchar(10),
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.64 sec)

Now you can insert some records in the table using insert command. The query is as follows:

mysql> insert into CaseInsensitiveDistinctDemo(UserEmailId,UserPassword) values('John@gmail.com','john123');
Query OK, 1 row affected (0.15 sec)
mysql> insert into CaseInsensitiveDistinctDemo(UserEmailId,UserPassword) values('john@gmail.com','654321');
Query OK, 1 row affected (0.43 sec)
mysql> insert into CaseInsensitiveDistinctDemo(UserEmailId,UserPassword) values('Mike@gmail.com','999999');
Query OK, 1 row affected (0.14 sec)
mysql> insert into CaseInsensitiveDistinctDemo(UserEmailId,UserPassword) values('mike@gmail.com','334556');
Query OK, 1 row affected (0.16 sec)
mysql> insert into CaseInsensitiveDistinctDemo(UserEmailId,UserPassword) values('Carol@gmail.com','1010101');
Query OK, 1 row affected (0.13 sec)
mysql> insert into CaseInsensitiveDistinctDemo(UserEmailId,UserPassword) values('Larry@gmail.com','12345678');
Query OK, 1 row affected (0.20 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from CaseInsensitiveDistinctDemo;

The following is the output:

+----+-----------------+--------------+
| Id | UserEmailId     | UserPassword |
+----+-----------------+--------------+
|  1 | John@gmail.com  | john123      |
|  2 | john@gmail.com  | 654321       |
|  3 | Mike@gmail.com  | 999999       |
|  4 | mike@gmail.com  | 334556       |
|  5 | Carol@gmail.com | 1010101      |
|  6 | Larry@gmail.com | 12345678     |
+----+-----------------+--------------+
6 rows in set (0.00 sec)

Here is the query to select case-insensitive distinct.

Case 1: Using UPPER(). The query is as follows:

mysql> select distinct upper(UserEmailId) from CaseInsensitiveDistinctDemo;

The following is the output:

+--------------------+
| upper(UserEmailId) |
+--------------------+
| JOHN@GMAIL.COM     |
| MIKE@GMAIL.COM     |
| CAROL@GMAIL.COM    |
| LARRY@GMAIL.COM    |
+--------------------+
4 rows in set (0.06 sec)

Case 2: Using LOWER(). The query is as follows:

mysql> select distinct lower(UserEmailId) from CaseInsensitiveDistinctDemo;

The following is the output:

+--------------------+
| lower(UserEmailId) |
+--------------------+
| john@gmail.com     |
| mike@gmail.com     |
| carol@gmail.com    |
| larry@gmail.com    |
+--------------------+
4 rows in set (0.00 sec)