How to Make SQL Case Sensitive String Comparison on MySQL

How can I make SQL case sensitive string comparison on MySQL?

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html

The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation:

col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin

If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation.

Case-sensitive string comparison in SQL (MariaDB, MySQL)

This should work:

WHERE BINARY column = 'data'

However, it will not work if you put column in single quotes. That would be a string as opposed to a column reference.

MySQL LIKE is case sensitive but I don't want it to be

Case sensitivity is based on the collation of the column you are searching, defined in your CREATE TABLE, or else the collation of the session, which determines the character set and collation of string literals.

Example:

CREATE TABLE `users_user` (
  `username` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into users_user set username='DEMO1-0048';

Here we see the default collation of utf8mb4_general_ci is case-insensitive:

mysql> select * from users_user where username like 'DeMO1-0048';
+------------+
| username   |
+------------+
| DEMO1-0048 |
+------------+

But if I force the column to use a case-sensitive collation:

mysql> select * from users_user where username collate utf8mb4_bin like 'DeMO1-0048';
Empty set (0.00 sec)

Or if I force the string literal to use a case-insensitive collation:

mysql> select * from users_user where username like 'DeMO1-0048' collate utf8mb4_bin;
Empty set (0.00 sec)

Or if I define the table with a case-sensitive collation:

CREATE TABLE `users_user` (
  `username` text COLLATE utf8mb4_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into users_user set username='DEMO1-0048';

mysql> select * from users_user where username like 'DeMO1-0048';
Empty set (0.00 sec)

So I would infer that your table is defined with a case-sensitive collation. You can check this:

mysql> select character_set_name, collation_name from information_schema.columns where table_name='users_user' and column_name='username';
+--------------------+----------------+
| character_set_name | collation_name |
+--------------------+----------------+
| utf8mb4            | utf8mb4_bin    |
+--------------------+----------------+

You can force a string comparison to be case-insensitive, even if the default collation defined for the table/column is case-sensitive.

mysql> select * from users_user where username like 'DeMO1-0048' collate utf8mb4_general_ci;
+------------+
| username   |
+------------+
| DEMO1-0048 |
+------------+

This works if you use the collate option on the column too:

mysql> select * from users_user where username collate utf8mb4_general_ci like 'DeMO1-0048';
+------------+
| username   |
+------------+
| DEMO1-0048 |
+------------+

Case Sensitive String Comparison

SQL Server has case sensitivity at the server, database, and column level. This is part of the collation properties. So in your example, it's likely that one or more of these settings has been set to case-insensitive.

-- Check server collation
SELECT SERVERPROPERTY('COLLATION')

-- Check database collation.
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

-- Check column collation
select table_name, column_name, collation_name
from information_schema.columns
where table_name = @table_name

Something like this SQL might work for you:

If (@USERNAME='rajat' COLLATE Latin1_General_CS_AS)
.....

SQL ORDER BY a string value: What is it comparing? (Case sensitive?)

In MySQL, it depends on the effective collation. Collation is the set of rules that determine the position of characters in an ordered set and what characters are considered equal, and typically involve natural language rules. For example, Spanish used to have ch as an independent letter located betwen c and d and then switched to being just individual c and h; MySQL has collations for both.

You can see available collations with these commands:

SHOW COLLATION; -- Display all
SHOW COLLATION WHERE charset = 'utf8mb4'; -- Filter by encoding

SELECT * FROM a JOIN b ON (BINARY a.text = BINARY b.text)

SELECT 
  LOCATE('ingredients', description) AS match_both_1,
  LOCATE('Ingredients', description) AS match_both_2,
  LOCATE(CAST('ingredients' AS BINARY), CAST(description AS BINARY)) AS match_second,
  LOCATE(CAST('Ingredients' AS BINARY), CAST(description AS BINARY)) AS match_first
FROM product

| match_both_1 | match_both_2 | match_second | match_first |
|     18       |     18       |      0       |     18      |
|     18       |     18       |     18       |      0      |