Have an account? Sign in
Login  Register  Facebook
how to ignore the different between upper and lower case
Hello
please how can i ignore the different between upper and lower case in my search
thank you !
Started: September 19, 2011 Latest Activity: September 19, 2011 php upper lower
3 Answers
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

Posted: MacOS
In: September 19, 2011

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.

Posted: MacOS
In: September 19, 2011

MySQL's VARCHAR and CHAR fields already ignore case when used in comparisons, including ORDER BY. You don't have to do anything special to get that.
and you can store the data in the mySQL in lower case and just perform query on it.

Posted: xtremex
In: September 19, 2011

Your Answer

xDo you want to answer this question? Please login or create an account to post your answer