How can I ignore the difference between upper and lower case when searching with mysql

Question

How can I ignore the difference between upper and lower case when searching with mysql

Answer 1

Do something like this:

SELECT user 
FROM users 
WHERE UPPER( user ) = UPPER( 'moustafa' );

Basically you're converting your result to one case and comparing against the search term which is also converted to upper case, effectively ignoring case.

Answer 2

The UPPER and LOWER functions can be used, but you can also affect the case-sensitivity by selecting the appropriate collation and/or column type.

For example, latin1_general_cs is case-sensitive with both VARCHAR and VARBINARY:

DROP TABLE IF EXISTS `case_sensitive`;
CREATE TABLE `case_sensitive` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `nonbinary` VARCHAR(255),
    `binary`  VARBINARY(255),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB COLLATE latin1_general_cs;

INSERT INTO `case_sensitive` (`nonbinary`, `binary`) VALUES ('A', 'A');

SELECT * FROM `case_sensitive` WHERE `nonbinary` = 'A';

+----+-----------+--------+
| id | nonbinary | binary |
+----+-----------+--------+
|  1 | A         | A      |
+----+-----------+--------+
1 row in set (0.00 sec)

SELECT * FROM `case_sensitive` WHERE `binary` = 'A';

+----+-----------+--------+
| id | nonbinary | binary |
+----+-----------+--------+
|  1 | A         | A      |
+----+-----------+--------+
1 row in set (0.00 sec)

SELECT * FROM `case_sensitive` WHERE `nonbinary` = 'a';

Empty set (0.00 sec)

SELECT * FROM `case_sensitive` WHERE `binary` = 'a';

Empty set (0.00 sec)

Whereas latin1_general_ci is case-insensitive with VARCHAR, and case-sensitive with VARBINARY:

DROP TABLE IF EXISTS `case_insensitive`;
CREATE TABLE `case_insensitive` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `nonbinary` VARCHAR(255),
    `binary`  VARBINARY(255),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB COLLATE latin1_general_ci;

INSERT INTO `case_insensitive` (`nonbinary`, `binary`) VALUES ('A', 'A');

SELECT * FROM `case_insensitive` WHERE `nonbinary` = 'A';

+----+-----------+--------+
| id | nonbinary | binary |
+----+-----------+--------+
|  1 | A         | A      |
+----+-----------+--------+
1 row in set (0.00 sec)

SELECT * FROM `case_insensitive` WHERE `binary` = 'A';

+----+-----------+--------+
| id | nonbinary | binary |
+----+-----------+--------+
|  1 | A         | A      |
+----+-----------+--------+

SELECT * FROM `case_insensitive` WHERE `nonbinary` = 'a';

+----+-----------+--------+
| id | nonbinary | binary |
+----+-----------+--------+
|  1 | A         | A      |
+----+-----------+--------+

SELECT * FROM `case_insensitive` WHERE `binary` = 'a';

Empty set (0.00 sec)

You should therefore pick a collation and column type that is most suited to your needs. You can find more information here:

Case Sensitivity in String Searches
http://dev.mysql.com/doc/refman/5.1/en/case-sensitivity.html

Character Sets and Collations in MySQL
http://dev.mysql.com/doc/refman/5.1/en/charset-mysql.html

Character Sets and Collations That MySQL Supports
http://dev.mysql.com/doc/refman/5.1/en/charset-charsets.html