# MySQL case insensitive select

## The Question :

250 people think this question is useful

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.

• Ultimately it depends on filed collation – if it’s ‘_ci’ (case-insensitive) or ‘_cs’ (case-sensitive)
• This is one poorly worded question ;). Half the answers are showing you how to do case insensitive comparison, half are aiming for case sensitive. And only 1 tells you that the default is in fact case insensitive. 🙂 It’s worth noting that case insensitivity works even when you do a comparison like 'value' in ('val1', 'val2', 'val3')
• @SaltyNuts man, reading this question 7 years later and realizing how much of a noob I was is embarrassing! I could have just read the documentation and the answer is in like the first sentence about SELECT statements…
• To add to what @JovanPerovic said, utf8_bin also makes it case sensitive. Not sure if that existed back then

507 people think this answer is useful

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

122 people think this answer is useful

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

53 people think this answer is useful

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

47 people think this answer is useful

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

25 people think this answer is useful

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.

16 people think this answer is useful

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

9 people think this answer is useful

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.

3 people think this answer is useful

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

3 people think this answer is useful

Try with:

order by lower(column_name) asc;



2 people think this answer is useful

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.

2 people think this answer is useful

The currently accepted solution 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.

0 people think this answer is useful

You can try it. hope it will be useful.

SELECT * FROM table WHERE Value COLLATE latin1_general_cs = "IAreSavage"



0 people think this answer is useful

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;

0 people think this answer is useful

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)