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 !
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 2 of 2 people found this answer helpful. Did you? Yes No
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 1 of 1 people found this answer helpful. Did you? Yes No
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 1 of 1 people found this answer helpful. Did you? Yes No |
© Advanced Web Core. All rights reserved