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

How can I ignore the differenc开发者_StackOverflowe between upper and lower case when searching with mysql

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.

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

Generally, you should use WHERE UPPER(COLUMNNAME) = UPPER('valuetocompare').

Alternatively, you culd use WHERE UPPER(COLUMNNAME) like UPPER('%valuetocompare%') if you want to do a substring search

i use utf8_unicode_(CI)-case-insensitive collate on the column your searching....and it works

you can store the data in the mySQL in lower case and just perform query on it.

SELECT * FROM table_name WHERE column_name1 LIKE '%A%' OR column_name1 LIKE '%a%';