MySQL: is a SELECT statement case sensitive?

Answer

Solution:

They are case insensitive, unless you do a binary comparison.

Answer

Solution:

You can lowercase the value and the passed parameter :

SELECT * FROM `table` WHERE LOWER(`Value`) = LOWER("IAreSavage")
Copy code

Another (better) way would be to use theCOLLATE operator as said in the documentation

Answer

Solution:

Comparisons are case insensitive when the column uses a collation which ends with (such as the defaultlatin1_general collation) and they are case sensitive when the column uses a collation which ends with{-code-3} or{-code-4} (such as theutf8_unicode{-code-3} andutf8{-code-4} collations).

Check collation

You can check your server, database and connection collations using:

mysql> show variables like '%collation%';
+     

Answer

---------+ Copy code

and you can check your table collation using:

mysql> SELECT table_schema, table_name, table_collation 
       FROM information_schema.tables WHERE table_name = `mytable`;
+     

Answer

---------+ | myschema | mytable | latin1_swedish_ci | Copy code

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;
Copy code

Your comparisons should now be case-sensitive.

Answer

--+

Answer

---------+ | Variable_name | Value | +

Answer

--+

Answer

---------+ | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +

Answer

--+

Answer

---------+|||mysql> SELECT table_schema, table_name, table_collation FROM information_schema.tables WHERE table_name = `mytable`; +

Answer

--+

Answer

--+

Answer

---------+ | table_schema | table_name | table_collation | +

Answer

--+

Answer

--+

Answer

---------+ | myschema | mytable | latin1_swedish_ci ||||-- 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;

Answer

Solution:

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

Answer

Solution:

String comparison in WHERE phrase is not case sensitive. You could try to compare using

WHERE `colname` = 'keyword'
Copy code

or

WHERE `colname` = 'KeyWord'
Copy code

and you will get the same result. That is default behavior of MySQL.

If you want the comparison to be case sensitive, you could addCOLLATE just like this:

WHERE `colname` COLLATE latin1_general_cs = 'KeyWord'
Copy code

That SQL would give different result with this one: WHEREcolname COLLATE latin1_general_cs = 'keyword'

latin1_general_cs is common or default collation in most database.

Answer

Solution:

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

Answer

Solution:

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;
Copy code

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.

Answer

Solution:

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

Answer

Solution:

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 useLOWER 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.

Answer

Solution:

Try with:

order by lower(column_name) asc;
Copy code

Answer

Solution:

Note also that table names are case sensitive on Linux unless you set thelower_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" 
Copy code

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

Answer

Solution:

You can try it. hope it will be useful.

SELECT * FROM `table` WHERE `Value` COLLATE latin1_general_cs = "IAreSavage"
Copy code

Answer

Solution:

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;

Answer

Solution:

In my case neither BINARY nor COLLATE nor CHARACTER SET works with my UTF8 table.

I have usernames in my table like henry, Henry, susan, Susan or suSan and find the respective users by comparing the byte sequences of the names.

The following function creates the byte sequences:

function makeByteString($string){
    $tmp = "";
    for($i=0;$i<strlen($string);$i++){
        $sign = substr($string,$i,1);
        $tmp.=ord($sign);
    }
    return $tmp;
}
Copy code

The SQL query finds the correct id:

$sql = "SELECT id, username FROM users WHERE `username` = ? ";
$stmt = $conn->prepare($sql);
$stmt->execute([$strUsername]); //e.g. susan, Susan or suSan
$rows = $stmt->rowCount();
if($stmt && $rows>0){
  while ($row = $stmt->fetch()) {
    if(makeByteString($strUsername) == 
                   makeByteString(trim($row["username"]))){
      $id = $row['id'];
    }
  }
}   
Copy code