MySQL is a SELECT statement case sensitive

Question

Can anyone tell me if a MySQL SELECT query is case sensitive or case insensitive by default  And if not  what query would I have to send so that I can do something like   SELECT   FROM  table  WHERE  Value     iaresavage    Where in actuality  the real value of Value is IAreSavage

User · Answer

You can try it  hope it will be useful   SELECT   FROM  table  WHERE  Value  COLLATE latin1 general cs    IAreSavage

User · Answer

String comparison in WHERE phrase is not case sensitive  You could try to compare using  WHERE  colname     keyword    or  WHERE  colname     KeyWord    and you will get the same result  That is default behavior of MySQL   If you want the comparison to be case sensitive  you could add COLLATE just like this   WHERE  colname  COLLATE latin1 general cs    KeyWord    That SQL would give different result with this one      WHERE colname COLLATE latin1 general cs    keyword   latin1 general cs is common or default collation in most database

User · Answer

SQL Select is not case sensitive   This link can show you how to make is case sensitive  http   web archive org web 20080811231016 http   sqlserver2000 databases aspfaq com 80 how-can-i-make-my-sql-queries-case-sensitive html

User · Answer

Note also that table names are case sensitive on Linux unless you set the lower case table name config directive to 1   This is because tables are represented by files which are case sensitive in Linux     Especially beware of development on Windows which is not case sensitive and deploying to production where it is   For example    SELECT   from mytable     against table myTable will succeed in Windows but fail in Linux  again  unless the abovementioned directive is set   Reference here  http   dev mysql com doc refman 5 0 en identifier-case-sensitivity html

User · Answer

They are case insensitive  unless you do a binary comparison

User · Answer

The collation you pick sets whether you are case sensitive or not

User · Answer

For anyone who would find himself in a similar situation like me  I add my solution using like  In my case  I had to select all the rows filtering them by a certain column value  In that column  there were different values  such as  project process    PROJECT process    PROJECT PROCESS  and so on  Notes  PROJECT project refers to a certain project name in capital lowercase letters  PROCESS process refers to a certain process name in capital lowercase letters  This query was the solution  SELECT   FROM  table name  where process like   project process     this query allowed me to get all the possible combinations

User · Answer

Marc B s answer is mostly correct  If you are using a nonbinary string  CHAR  VARCHAR  TEXT   comparisons are case-insensitive  per the default collation  If you are using a binary string  BINARY  VARBINARY  BLOB   comparisons are case-sensitive  so you ll need to use LOWER as described in other answers  If you are not using the default collation and you are using a nonbinary string  case sensitivity is decided by the chosen collation  Source  https   dev mysql com doc refman 8 0 en case-sensitivity html  Read closely  Some others have mistaken it to say that comparisons are necessarily case-sensitive or insensitive  This is not the case

User · Answer

You can lowercase the value and the passed parameter    SELECT   FROM  table  WHERE LOWER  Value     LOWER  IAreSavage     Another  better  way would be to use the COLLATE operator as said in the documentation

User · Answer

String fields with the binary flag set will always be case sensitive  Should you need a case sensitive search for a non binary text field use this  SELECT  test  REGEXP BINARY  TEST  AS RESULT

User · Answer

Comparisons are case insensitive when the column uses a collation which ends with  ci  such as the default latin1 general ci collation  and they are case sensitive when the column uses a collation which ends with  cs or  bin  such as the utf8 unicode cs and utf8 bin collations     Check collation  You can check your server  database and connection collations using   mysql gt  show variables like   collation     ---------------------- -------------------    Variable name          Value                ---------------------- -------------------    collation connection   utf8 general ci       collation database     latin1 swedish ci     collation server       latin1 swedish ci    ---------------------- -------------------    and you can check your table collation using   mysql gt  SELECT table schema  table name  table collation         FROM information schema tables WHERE table name    mytable    ---------------------- ------------ -------------------    table schema           table name   table collation      ---------------------- ------------ -------------------    myschema               mytable      latin1 swedish ci     Change collation  You can change your database  table  or column collation to something case sensitive as follows   -- Change database collation ALTER DATABASE  databasename  DEFAULT CHARACTER SET utf8 COLLATE utf8 bin   -- or change table collation ALTER TABLE  table  CONVERT TO CHARACTER SET utf8 COLLATE utf8 bin   -- or change column collation ALTER TABLE  table  CHANGE  Value        Value  VARCHAR 255  CHARACTER SET utf8 COLLATE utf8 bin    Your comparisons should now be case-sensitive

User · Answer

Try with    order by lower column name  asc

User · Answer

The default is case insensitive  but  the next most important thing you should take a look at is how the table was created in the first place  because you can specify case  sensitivity when you create the table    The script below creates a table   Notice down at the bottom it says  COLLATE latin1 general cs    That cs at the end means case sensitive   If you wanted your table to be case insensitive you would either leave that part out or use  COLLATE latin1 general ci       CREATE Table PEOPLE           USER ID  INTEGER UNSIGNED NOT NULL AUTO INCREMENT          FIRST NAME  VARCHAR 50  NOT NULL         LAST NAME  VARCHAR 50  NOT NULL          PRIMARY KEY  USER ID            ENGINE MyISAM DEFAULT CHARACTER SET latin1     COLLATE latin1 general cs AUTO INCREMENT 0    If your project is such that you can create your own table  then it makes sense to specify your case sensitivity preference when you create the table

User · Answer

USE BINARY  This is a simple select  SELECT   FROM myTable WHERE  something     Something     1  This is a select with binary  SELECT   FROM myTable WHERE BINARY  something     Something   or  SELECT   FROM myTable WHERE  something    BINARY  Something     0

[mysql] MySQL: is a SELECT statement case sensitive?

Examples related to mysql

Examples related to select